texts: Known Good SQL Logins Failing (Error 18456) Accompanied by “Unable to Reuse a Session” (Error 18056)

texts:
I am losing my patience with this problem.

I did see a couple of other questions here that are similar, but I've already looked at the fix actions that resolved their issues and they did not apply to this scenario.

I'm running SQL Server 2008 R2 with all updates. When this particular issue occurs my servers lose all connection ability to the server and it has to be restarted.

It is very intermittent. Every now and again this SQL server instance will report the error: Login failed for user 'XX'. Reason: Failed to open the database configured in the login object while revalidating the login on the connection. Error: 18456, Severity: 14, State: 46.

I know this is a good login, it works all of the other times, and no one changes the credentials.

The above error is immediately followed by this error:

The client was unable to reuse a session with SPID 150, which had been reset for connection pooling. The failure ID is 46. This error may have been caused by an earlier operation failing. Check the error logs for failed operations immediately before this error message.

Error: 18456, Severity: 14, State: 46.

When this occurs a few minutes later this is reported also:

Message All schedulers on Node 0 appear deadlocked due to a large number of worker threads waiting on ASYNC_NETWORK_IO. Process Utilization 0%.

The above message just keeps repeating until I restart the server.

From researching this issue I've increased the maximum worker threads (currently at 1100). The server has 72G memory and 4 vCPUs, running Windows Server 2008 R2.

I don't encounter this issue at all on my other database servers that run the same environment. Just this one. Any help would be greatly appreciated, I'm running out of options.


texts: MySQL - installed version 5.5 but mysql --version says 5.0.95

texts:
I'm running CentOS 5.2 and upgraded MySQL to 5.5. When I run mysql I get this:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 5.5.45 MySQL Community Server (GPL)

But when I run mysql --version I get this:

mysql  Ver 14.12 Distrib 5.0.95, for redhat-linux-gnu (x86_64) using readline 5.1

Since the mysqld is mysql55-mysqld I'm sure I'm running 5.5, but why am I getting the wrong version with the version command?


texts: Database Full Backup fails being deadlock victim

texts:
On one of my SQL Server 2008 R2 Databases, Full backup job fails about twice a month due to Deadlocks. Transaction Log backup is only other job which is scheduled to execute at the same time.
Can you help me investigate what causes deadlock on backup operation?


texts: Why does this CTE return the bulk of its results immediately, but then take several minutes to finish?

texts:
I've got a schema roughly like this (this is a simplification of my actual schema):

CREATE TABLE foo (
    key1 NUMERIC(6) NOT NULL,
    key2 VARCHAR(32) NOT NULL,
    val VARCHAR(255) NULL,
    CONSTRAINT foo_pk PRIMARY KEY (key1, key2) --PK on key1, key2
    )
GO
CREATE TABLE bar (
    key1 NUMERIC(6) NOT NULL,
    key2 VARCHAR(32) NOT NULL,
    val VARCHAR(255) NULL,
    CONSTRAINT bar_pk PRIMARY KEY (key1, key2) --PK on key1, key2
    )
GO
CREATE TABLE aliases (
    id VARCHAR(32) NOT NULL PRIMARY KEY,
    text VARCHAR(255) NOT NULL,
    CONSTRAINT aliases_uk UNIQUE (text) --PK on id, unique constraint on text
    )
GO

INSERT INTO aliases (id, text)
SELECT REPLACE(NEWID(), '-', ''), 'Text1' UNION ALL
SELECT REPLACE(NEWID(), '-', ''), 'Text2' UNION ALL
SELECT REPLACE(NEWID(), '-', ''), 'Text3' UNION ALL
SELECT REPLACE(NEWID(), '-', ''), 'Text4' UNION ALL
SELECT REPLACE(NEWID(), '-', ''), 'Text5' UNION ALL
SELECT REPLACE(NEWID(), '-', ''), 'Text6' UNION ALL
SELECT REPLACE(NEWID(), '-', ''), 'Text7' UNION ALL
SELECT REPLACE(NEWID(), '-', ''), 'Text8' UNION ALL
SELECT REPLACE(NEWID(), '-', ''), 'Text9' UNION ALL
SELECT REPLACE(NEWID(), '-', ''), 'Text10' UNION ALL
SELECT REPLACE(NEWID(), '-', ''), 'Text11' UNION ALL
SELECT REPLACE(NEWID(), '-', ''), 'Text12' UNION ALL
SELECT REPLACE(NEWID(), '-', ''), 'Text13' UNION ALL
SELECT REPLACE(NEWID(), '-', ''), 'Text14' UNION ALL
SELECT REPLACE(NEWID(), '-', ''), 'Text15' UNION ALL
SELECT REPLACE(NEWID(), '-', ''), 'Text16';
GO

BEGIN
    DECLARE @i INT = 1;
    WHILE (@i <= 234)
    BEGIN
        INSERT INTO foo (key1, key2, val) SELECT @i, id, id FROM aliases;
        SET @i = @i + 1;
    END;
    INSERT INTO bar (key1, key2, val) SELECT key1 + 234, key2, val FROM foo;
END;
GO

I want to get a list of all key1 and key2 pairs (using the user-friendly names for key2, with the user-friendly aliases for the vals concatenated. My initial attempt looks like this:

WITH
foos_and_bars (key1, key2, val) AS (
    SELECT key1, key2, val FROM foo UNION ALL
    SELECT key1, key2, val FROM bar),
texts (key1, key2_name, val_text, rnum) AS (
    SELECT key1, a1.text, a2.text, ROW_NUMBER() OVER (PARTITION BY key1, key2 ORDER BY val)
    FROM foos_and_bars
    JOIN aliases a1 ON a1.id = foos_and_bars.key2
    LEFT JOIN aliases a2 ON a2.id = foos_and_bars.val),
partitioned (key1, key2_name, val_text, rnum, maxnum) AS (
    SELECT key1, key2_name, val_text, rnum, MAX(rnum) OVER (PARTITION BY key1, key2_name)
    FROM texts),
recurse (key1, key2_name, val_texts, rnum, maxnum) AS (
    SELECT key1, key2_name, val_text, rnum, maxnum
    FROM partitioned
    WHERE rnum = 1
    UNION ALL
    SELECT r.key1,
        r.key2_name,
        CAST(r.val_texts + CHAR(13) + CHAR(10) + p.val_text AS VARCHAR(255)),
        p.rnum,
        r.maxnum
    FROM recurse r
    JOIN partitioned p
    ON p.key1 = r.key1
    AND p.key2_name = r.key2_name
    AND p.rnum = r.rnum + 1)
SELECT * FROM recurse WHERE rnum = maxnum;

It works, and I get 7480 rows in less than a second, but then SSMS runs for no less than 2 minutes and 40 seconds puzzling out the last 8 rows. These last rows are no more complex than the first (in fact, with the data currently in the system, rnum and maxnum are never greater than 1), and as these tables were created exclusively for mocking on this SE question, there should be no locks.

The issue seems to be limited to the recursive CTE, as selecting from the top down to partitioned yields 7488 rows in less than a second. What could the CTE be getting stuck on?

Execution plans (using a setup that matches the simplified schema):

Plan for all records
Records for only one key1

The output is going into a view that is going to back a Sybase window, which will be used for searching records. As such, it needs to be capable of retrieving all the rows, though the program pulls 1000 rows at a time by default.


texts: What happens when you have two events scheduled to run after identical n seconds?

texts:
I am running a survey and i am getting lots of data and i need to process that data quickly from within mysql.I am using a mysql events to help me process the hundreds of rows that i have.

I have written this routine that i am calling from an event.

DELIMITER $$
CREATE PROCEDURE survey_task()
BEGIN

select tel_number, text_message, id INTO @t_number, @t_message, @t_id from incoming_messages where t_status = 0 limit 1;

insert into surveyed_lot(telephone_number, survey_answer,date_of_insert )
        values(@t_number, @t_message,now());

UPDATE incoming_messages SET t_status = 1 WHERE id = @t_id;

END $$
DELIMITER ;

When i process a row, i mark that row with 1 to show that is read and the ones that are not read read 0.

This is the event i am calling the routine from

CREATE EVENT survey_processor_1
    ON SCHEDULE EVERY 10 SECOND
    DO
       call survey_task();

My question is,if i have another identical event that calls survey_task at exactly the same time as survey_processor_1

CREATE EVENT survey_processor_2
    ON SCHEDULE EVERY 10 SECOND
    DO
       call survey_task();

will survey_task be called twice or does mysql have a way of making one of the two events to acquire some form of a lock and thus allow one to execute ahead of the other?.


texts: How to find when was partition created in SQL Server

texts:
In SQL Server, is there any way to find when were the partitions created on the table?

Background:
We have around 40 tables which are partitioned along with their indexes. We have total of 4 Filegroups. we have a windows service which is related to partitioning. what it does is, it creates new partitions if the number of records that reached some threshold(other logics are also there) and these partitions are created in one of the 4 filegroups in round robin fashion. we have lot of partitions. I want to know is there anywhere inside SQL Server, does it store the creation time of these partitions?

If not I will be shocked as I feel this is the basic which should be present in SQL Server no matter what. Please provide your inputs.


texts: Select entries based on a combination of columns

texts:
I have a table with a definition like this:

CREATE TABLE [dbo].[Contact]
(
    ClientNumber [numeric] (20, 0) NULL,
    ContactDate [Date] NULL,
    Name [text] NULL,
    AdditionalInformation [text] NULL
)

This table is filled with information for a single month. Each client may have multiple entries for a single day. Meaning there might be entries like this:

42  2015-12-09  Felix       called today
42  2015-12-09  Fexclicx    ...

My goal is to get all client results from the table with the condition that there should be only one entry a day per client number. I do not care which of the (here) two entries is displayed, so something like top 1.

So far I tried to achieve this with a distinct:

select distinct ClientNumber, ContactDate, * from Contact

Unfortunately this does not limit the results to show a client entry for a specific date only once. They still all do appear. Also the problem is that there are no id's in this table so I cannot work with that.

How could I achieve getting all entries but only one entry for one client per day?


texts:
I am building a new environment using the SQL Server 2017 Always-On system.

This will be active 24/7 and there will be lots of DML processes.

I have a 100GB SAS for the OS, a 1TB SAS for Data and a 1TB SSD disk for the TempDB and log files.

C:/ - 100GB - SAS
D:/ - 1TB - SAS - Data files
E:/ - 1TB - SSD - Log/TempDB Files

Is the above structure correct for best performance/best practice? Or should I change Log/Tempfile to SAS instead of Datafiles ?

For a single production database, how many Data and Log files do I need to create? 1 data file and 1 log file is best practice?


texts: Why does my SQL Server show more than half a million active tasks?

texts:
select task_state,count(*)
from sys.dm_os_tasks
group by task_state

I ran the above statement on a SQL Server instance, and found it had about 633,000 records.

task_state
-------------- -----------
RUNNABLE       2
RUNNING        32
DONE           633115
SUSPENDED      99

How can I Close/Kill the useless tasks?

The MDW data collector have about 4000 page allocate in tempdb per time.

And this cause the IO pressure when server in busy time.

This is production server, We do not want to restart the service.
And the version number is 11.0.3000.

The Max Worker Count is :1216
CPU Count:48
Hyperthread ratio:12
MaxDop: 8

Scheduler_Id is 0 - 47 and the amount of rows are average.
and other columns are null.


texts: Restore a point between two full backups

texts:
I make two full backups on Oct 1 and Oct 10. I want to restore the server to a state in Oct 5. So I just do as follows:

Perform a transaction log backup on the server on Oct 23. I have never backup transaction log in the past.
Restore the server with Oct 1 full backup with NORECOVERY option.
Try to restore to the point at Oct 5 12:00, with the transaction log.

But the restore fails and  SQL Server said the transaction log does not contain the point. The point is too early. Why? Also my .LDF file is about 13G, but the transaction log backup is only 200MB. Why?

Thanks


texts: My most recent expensive query is sp_procedure_param_rowset

texts:
I'm stress testing our software running many transactions and reports at the same time on one same DB. analyzing the most recent expensive queries, I found that the top query is the sp_procedure_param_rowset.  Research on sp_procedure_param_rowset shows me that it is when an API calls a Stored procedure and get the metadata of the parameters of each stored procedure.
What I'm trying to figure out and I can't find:
1. Is this the same as parameter Sniffing issue?
2. how can I minimize the impact of Sp_procedure_param_rowset?

It's an undocumented system procedure...

Please help
thank you
John


texts: How can I speed SQLPS connection when I don't have WMI permissions at the server

texts:
When connecting SQLPS to a SQL Server, it will try to run some WMI service status queries. In an environment where one does not have permissions for those, PowerShell issues a bunch of warnings, and it's VERY slow to connect.

It's possible to suppress the warning messages with $WarningPreference = 'SilentlyContinue', but is there any way to disable the actual checks, in order to speed performance?

Example:

PS SQLSERVER:\> set-location \sql\myserver\default
WARNING: Could not obtain SQL Server Service information. An attempt to connect to WMI on '<servername>' failed with the following error: Access is denied. (Exception from HRESULT: 0x80070005 (E_ACCESSDENIED))
WARNING: Could not obtain SQL Server Service information. An attempt to connect to WMI on '<servername>' failed with the following error: Access is denied. (Exception from HRESULT: 0x80070005 (E_ACCESSDENIED))
WARNING: Could not obtain SQL Server Service information. An attempt to connect to WMI on '<servername>' failed with the following error: Access is denied. (Exception from HRESULT: 0x80070005 (E_ACCESSDENIED))
WARNING: Could not obtain SQL Server Service information. An attempt to connect to WMI on '<servername>' failed with the following error: Access is denied. (Exception from HRESULT: 0x80070005 (E_ACCESSDENIED))
WARNING: Could not obtain SQL Server Service information. An attempt to connect to WMI on '<servername>' failed with the following error: Access is denied. (Exception from HRESULT: 0x80070005 (E_ACCESSDENIED))
WARNING: Could not obtain SQL Server Service information. An attempt to connect to WMI on '<servername>' failed with the following error: Access is denied. (Exception from HRESULT: 0x80070005 (E_ACCESSDENIED))
WARNING: Could not obtain SQL Server Service information. An attempt to connect to WMI on '<servername>' failed with the following error: Access is denied. (Exception from HRESULT: 0x80070005 (E_ACCESSDENIED))
WARNING: Could not obtain SQL Server Service information. An attempt to connect to WMI on '<servername>' failed with the following error: Access is denied. (Exception from HRESULT: 0x80070005 (E_ACCESSDENIED))

Scenario: SQL Agent job using a PowerShell job step takes 30+ seconds just to spin up and connect to the local SQL Server, before doing any work.


texts: Can't set log_bin_trust_function_creators=1 on machine startup - MySQL 8.0

texts:
I'm trying to put the log_bin_trust_function_creators=1 parameter into the my.cnf configuration file to set the variable correctly on startup on a machine with Ubuntu 20.04 LTS. But every time I save the file with this line written in the config file,  when I try to access the db I can't log in and iIhave this error output message:

mysql: [ERROR] Found option without preceding group in config file /etc/mysql/my.cnf at line 23.
mysql: [ERROR] Fatal error in defaults handling. Program aborted!

When I use SET GLOBAL log_bin_trust_function_creators=1; the parameter and the db work fine, but I would prefer to not having to reboot the machine when I set this parameter.
The version that we're using is mysql  Ver 8.0.20-0ubuntu0.20.04.1 for Linux on x86_64 ((Ubuntu))
Can someone help me with this problem?


texts:
I am aware that this or similar has been asked in other places. None of the other answers have worked for me.

Machine is a Macbook Pro 2014 / OS X Yosemite 10.10 with Postgres 9.3.5 installed via Homebrew.

I can only connect to my local postgres instance if I do NOT specify a host. This works:

psql -Umyuser -d mydb
sql (9.4.4)
Type "help" for help.

myuser=#

-h localhost does not:

psql -Umyuser -d mydb -h localhost
psql: server closed the connection unexpectedly
    This probably means the server terminated abnormally
    before or while processing the request.

It pauses for a long time before I get this message.

I am trying to set up a third party tool that seems to want to specify localhost, so it is not an option just to leave that off.

If I understand what I've read correctly elsewhere, postgres behaves differently if you're using TCP/IP, and specifying -h localhost forces that. Accordingly I have added  the following in pg_hba.conf:

local   all             all                                     trust
# IPv4 local connections:
host    all             all             127.0.0.1/32            trust
host    all             all             localhost               trust
# IPv6 local connections:
host    all             all             ::1/128                 trust

(127.0.0.1 AND localhost seems redundant but desperation drives superstition) ... and in postgresql.conf:

listen_addresses = 'localhost'
port = 5432

netstat confirms that Postgres is listening to that port:

☁  ~  netstat -an | grep 5432
tcp4       0      0  127.0.0.1.5432         *.*                    LISTEN
tcp6       0      0  ::1.5432               *.*                    LISTEN
d8d7c0e31d5add1d stream 0 0 d8d7c0e326b18a1d 0 0 0 /tmp/.s.PGSQL.5432

All permissions are set on the socket file:

~ ls -l /tmp/.s.PGSQL.5432
srwxrwxrwx  1 fritz  wheel  0 12 Jul 13:27 /tmp/.s.PGSQL.5432

/etc/hosts has an entry for localhost:

127.0.0.1   localhost
::1         localhost

psql and pg_ctl are pointing to the same installations:

~ which psql
/usr/local/bin/psql
~ ll /usr/local/bin/psql
/usr/local/bin/psql -> ../Cellar/postgresql/9.4.4/bin/psql
~ which pg_ctl
/usr/local/bin/pg_ctl
~  ll /usr/local/bin/pg_ctl
/usr/local/bin/pg_ctl -> ../Cellar/postgresql/9.4.4/bin/pg_ctl

There's nothing named psql elsewhere on my system that seems like it could be hijacking this process:

~  sudo find / -name psql
/Applications/pgAdmin3.app/Contents/SharedSupport/psql
/Users/fritz/devtools/phantomjs/src/qt/config.tests/unix/psql
/Users/fritz/Qt/5.3/Src/qtbase/config.tests/unix/psql
/Users/fritz/Qt/5.3/Src/qtbase/src/plugins/sqldrivers/psql
/Users/fritz/Qt/5.3/Src/qtbase/src/sql/drivers/psql
/usr/local/bin/psql
/usr/local/Cellar/postgresql/9.4.4/bin/psql

UPDATE: telnet connects to localhost 5432 for a while and then is disconnected:

~  telnet localhost 5432
Trying ::1...
Connected to localhost.
Escape character is '^]'.
Connection closed by foreign host.

UPDATE 2: I used wireshark to spy on tcp localhost port 5432. Trouble is, I don't know what I should see and whether/how the results are wrong. Hoping someone out there can make something of this capture file.


texts: psql with '-c' option or '-f' option is not terminating properly

texts:
When I run a command such psql -d database -c 'select * from table' or psql -d database -f sqlcommands psql does not terminate cleanly. Interestingly piping the the contents of the file instead of using the -foption eg, psql -d database < sqlcommands doesn't display this behaviour.

I need to press the q key as required by less or man for the execution to finish and return to the command prompt?

Is that the standard behaviour? Are the some escapes required that will allow it to terminate cleanly?


texts:
As per the subject, I'm receiving an error on an InnoDB table: "table is full" when it reached 4GB the filesystem is far from being full so the only thing that I'm thinking right now is that the issue is with the fact that the MySQL installation is a 32-bit.
Can this be the case even if we are in a linux environment?


texts:
I have a busy InnoDB table that expands and contracts regularly in size on a daily basis based on it's load. Typically, we'd see the table grow to 80GB in size (50/50 data and indexes) and then shrink down to around 30GB. Unfortunately, InnoDB doesn't like freeing up space it's not using anymore which doesn't bode well for our 120GB SSD.

Typically, an optimize (or recreate for InnoDB tables) would give us a bit more breathing space. But sometimes there isn't enough room for the temporary table to be created.

Despite setting a different location for the temporary table variable in the my.cnf, the InnoDB database still insists in creating temporary tables in it's data directory, within the database folder. Is there any way I can point MySQL to a different drive when creating temporary InnoDB tables?

I'm sure it would do more damage than good, but it was suggested here that we could potentially create a symlink for the database on a larger drive, then symlink the tables to the SSD. That way, all standard operations will run on the much faster SSD and routine maintenance tasks will run on the larger, slower drive.

EDIT: I should clarify. innodb_file_per_table is set and so is tmpdir (to the larger drive), but tmpdir seems to do nothing for me.


texts: How to troubleshoot how resource governed query is being throttled in SQL 2012?

texts:
I have a parallelized query on SQL 2012 SP1 (ent) that is running in a user defined resource governor pool much much slower (2 seconds versus 125 to 140 seconds under user defined pool = ~75 times slower) than the same query that runs in the default resource pool.

The query plan hash is identical between the two executions and statistics io returns same values for all operations. I've confirmed that execution plan does not have additional spill warnings (both have one), and that the memory grant for the queries are identical.

User defined pool workload group

<QueryPlan DegreeOfParallelism="4" MemoryGrant="2384096" CachedPlanSize="384" CompileTime="3608" CompileCPU="2712" CompileMemory="15672">
        <ThreadStat Branches="5" UsedThreads="20">
          <ThreadReservation NodeId="0" ReservedThreads="20" />
        </ThreadStat>
        <MemoryGrantInfo SerialRequiredMemory="5632" SerialDesiredMemory="2360768" RequiredMemory="28960" DesiredMemory="2384096" RequestedMemory="2384096" GrantWaitTime="0" GrantedMemory="2384096" MaxUsedMemory="93872" />
        <OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="209715" EstimatedPagesCached="104857" EstimatedAvailableDegreeOfParallelism="4" />

Default pool

<QueryPlan DegreeOfParallelism="4" MemoryGrant="2384096" CachedPlanSize="384" CompileTime="3016" CompileCPU="2660" CompileMemory="15672">
            <ThreadStat Branches="5" UsedThreads="20">
              <ThreadReservation NodeId="0" ReservedThreads="20" />
            </ThreadStat>
            <MemoryGrantInfo SerialRequiredMemory="5632" SerialDesiredMemory="2360768" RequiredMemory="28960" DesiredMemory="2384096" RequestedMemory="2384096" GrantWaitTime="0" GrantedMemory="2384096" MaxUsedMemory="88768" />
            <OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="209715" EstimatedPagesCached="104857" EstimatedAvailableDegreeOfParallelism="4" />

Total_worker_time is nearly identical between the queries. Only thing that seems different is the total_elapsed_time. Wait is cxpacket. At this point, I must think this is something that resource governor is doing to throttle the CPU and/or memory. I can't find any evidence of the throttling though. CPU throttled counter does not budge, neither does memory suboptimal plans/sec.

For the workload group I've played with increasing memory grant beyond default (25%), and have played with max dop settings. Nothing makes a difference to execution time unless I make the query execute serially. If I change maxdop of workload group to 1 (serial execution) the query runs in ~3 seconds once in cache (1 second slower than default group). All other levels of maxdop result in ~70 and 75 times slower).

Server info:
2 socket x 6 core server. Server MaxDop = 4. CPU is relatively at rest (~12% utilization) when I execute the tests. Memory on server is not under pressure (~6GB free on 24GB box).

Resource pool configuration

100% max memory

50% CPU (increasing to 100% didn't make any difference)

75% CPU CAP

Workload group

MaxDop 0 (and have tried 0,1,4,7)

Memory Grant (25% - and have tried SQL recommended maximum of 70%)

Any ideas on how to narrow down what is doing the throttling?
I do not see anything obvious from the Microsoft link for troubleshooting it

http://technet.microsoft.com/en-us/library/cc627395(v=sql.105).aspx

Full plan for Default Pool (quick) execution - http://pastebin.com/y0P9J61f

Full plan for User Pool (slow) execution - http://pastebin.com/20hnFSTW

The problem query is second statement in the plans (first one is inconsequential and doesn't run parallel anyways...)


texts: I have a list of Part numbers in a txt file.  I need to get the matching Inventory number for each from our JDE database. Is it possible to build a macro in I-Series to search a JDE database for matches from a text file?


texts: Is there a way to get the last NDF file READ/Access date / time?

texts:
I have found a mysterious NDF file in my DB. Can't tell if it contains valid data or if the file is dead.

I'd like to remove it but I cannot tell if the file is in use by SQL Server. Windows Last Modified Date is 2 years ago, but for SQL files that is not reliable because SQL uses it's own drivers to access its files.

How can I tell what the last access time (either to read or to write) is for particular NDF file in a db?

Thanks!


texts: Bulk SQL INSERT into Azure SQL Database using spark causes blocking/contention?

texts:
I am running the following code using microsoft's sql sparkconnector to write a 1-2 Billion dataframe into Azure SQL Database.
df.write \
.format("com.microsoft.sqlserver.jdbc.spark") \
.mode("append") \
.option("url", secrets.db.url) \
.option("dbtable", 'tableName') \
.option("user", secrets.db.user) \
.option("password", secrets.db.password) \
.option("batchsize", 1048576) \
.option("schemaCheckEnabled", "false") \
.option("BulkCopyTimeout", 3600) \
.save()

This is a snapshot of the DB Utilization graph
And These are the first few rows from the following query using sp_whoisactive
EXEC sp_WhoIsActive
@find_block_leaders = 1,
@sort_order = '[blocked_session_count] DESC'

The wait_info column's value is Resource_Semaphore.
Configs:
My dataframe is partitioned over 2100 partitions on a cluster of 900 cores
My database is 14 vcores in General Purpose tier on Azure.
My query is incredibly slow because of this blocking. It's almost like it's running one bulk insert from my cluster at a time. Any suggestions on what to change to speed it up? or any insights into why it's blocking?


texts:
i'm trying to make a query that shows the users and all the roles that they have, i already know how to ask about the roles of one particular user:

 SELECT oid, rolname FROM pg_roles WHERE
 pg_has_role( 'name_of_user', oid, 'member');

Any idea how to do it?


texts:
I have attached x_Data.mdf and x_Log.ldf file successfully with SQL Server, to replace the same file I deleted and tried to attach with new file having same name. But it is throwing below error statement.

Can anybody please shed some light on this?

  Msg 5173, Level 16, State 2, Line 1
  One or more files do not match the primary file of the database. If you are attempting to attach a database, retry the operation with the correct files.  If this is an existing database, the file may be corrupted and should be restored from a backup.


texts: What is seed in RAND function in SQL Server

texts:
I am not able to understand as to what is the use of seed in the RAND function of SQL Server?

I read the below link but i am not able to understand what it is doing?
Link 1
Link 2
Link 3

After trying out few random seed number i found the below results:-

Select RAND(1) - 0.713591993212924
Select RAND(2) - 0.713610626184182
Select RAND(3) - 0.71362925915544
Select RAND(4) - 0.713647892126698
Select RAND(5) - 0.713666525097956

Whenever i provide the seed from 1 to 5 i always get the number that i have mentioned above.

Why is that so?


texts: How to make SQL Server order fields by 'Id' (which in this case is an Identity column)

texts:
I have an SQL Server table that i post into using C#. I would like the fields to be ordered using Id. Instead i get something like this:

I need the id column to be arranged in order of increasing number because i use a data table in C# to go through these values one at a time, therefore jumping from 7 to 9 to 11 back to 8 is very inconvenient.

N.B: I'm not talking about select * from table_name order by id. I need the values to be 'permanently stored' in the order requested.


texts: ALTER USER statement modifies users on off-air node

texts:
I had previously asked a question about Orphaned Users here and was directed to this link in an effort to fix it, but this didn't seem to answer my questions, so I am re-asking after some further review of my system.

We found that several database users on our secondary AG node were orphaned and set as SQL User Without Login, so we were unable to perform a successful failover. The only fix was to run ALTER USER 'user' with login = 'user'; after failover, which mapped the Database login with the SQL login on the 2nd node and allowed the app to connect. Unfortunately, this removed the configuration from the users on the 1st node and set them to SQL User Without Login. We obviously don't want this, because it means manual intervention and additional downtime in an automatic failover.

My questions are:

Why does an ALTER USER statement on one node affect the other if invoked on that first node?
Is there a setting within the Availability Group properties that needs to be checked off/added that could be missing in my setup?
There was a recommendation in another blog that stated to drop the user, and re-create with the SID of the SQL User. How is this different than running an ALTER USER statement, as the SID changes on the DB user to match with the SQL User?


texts: Large number of VLF and shrinking the log

texts: If there are lot number of VLF then it will effect the performance of the queries. I saw that solution is to shrink the log file. But shrinking the file is also bad. So how to take control of whole thing? What is the optimal number of VLF in a database?


texts: How to check for null in column? and use other column instead?

texts:
How would I write a query that involves the following check:

if colA is null then colB = colB1 + colB2

if colA is not null then colB = colA


texts:
Hello I am trying to connect to mysql server from php using PDO.

But I get this error :

An error occured : SQLSTATE[HY000] [2002] Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)

Note that this is not a database I host. I was simply given the username and password to construct the database, create the users etc.

function ConnectToDb()
{
    try{
        $dns = 'mysql:host=1.1.1.1;dbname=dummyDbName';
        $username = 'dummyUser';
        $password = 'dummyPassword';
        $LINK = new PDO($dns, $username, $password);
        $LINK->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
        $LINK->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        if (!$LINK){
            die('Could not connect : ' .mysql_error());
        }
        else{
            return $LINK;
        }
    } catch (PDOException $ex){
        echo "An error occured : " .$ex->getMessage();
    }
}

I know that this works on localhost. I'm using it no problem, but as soon as I try to connect to the production database it fails.
Anyone has an hint?


texts: Returning a True or False from an IIF statement

texts:
I have a query with the following IIF statement

OnlineEnrolment: IIf([qry_ebs_final_CAN-ENROL-ONLINE].[SP_CAN_ENROL]='Y',True,False)

It gives the correct results, but the format that it gives them is what I am struggling with.  It responds back with 0 or -1.   I want it to respond TRUE or FALSE.

How can I change this other than making TRUE and FALSE strings?  I still want them as boolean values.

Thanks

Kieran


texts: Me and my friend had a website. Before closing website he dumped MySQL Database from phpmyadmin and stores it locally on his PC. Now he send me sql file. Is there a way to see if he didn't change/add/remove lines,tables and variables and other stuff from *.sql file ? How he can modify the file so I can't notice that file is modified ?


texts: Сardinality estimation of partially covering range predicates

texts:
At the moment I'm trying to figure out how SQL Server evaluates the cardinality of range predicates that partially cover the histogram step.

On the Internet, at cardinality-estimation-for-and-for-intra-step-statistics-value I came across a similar question and Paul White gave a rather interesting answer to it.

According to Paul's answer, the formulas for estimating cardinality for the predicates >= and > (in this case, I am only interested in the Cardinality estimator model of at least 120) are as follows:

For >:

Cardinality = EQ_ROWS + (AVG_RANGE_ROWS * (F * (DISTINCT_RANGE_ROWS - 1)))

For >=:

Cardinality = EQ_ROWS + (AVG_RANGE_ROWS * ((F * (DISTINCT_RANGE_ROWS - 1)) + 1))

I tested the application of these formulas on the [Production].[TransactionHistory] table of the AdventureWorks2014 database based on the range predicate using the TransactionDate column and datetime range between '20140614' and '20140618'.

The statistics for the histogram step of this range are as follows:

According to the formula, I calculated the cardinality for the following query:

SELECT COUNT(1)
FROM [AdventureWorks2014].[Production].[TransactionHistory]
WHERE [TransactionDate] BETWEEN '20140615 00:00:00.000' AND '20140616 00:00:00.000'

The calculation was performed using the following code:

  DECLARE @predStart DATETIME =  '20140615 00:00:00.000'
  DECLARE @predEnd DATETIME = '20140616 00:00:00.000'

  DECLARE @stepStart DATETIME = '20140614 00:00:00.000'
  DECLARE @stepEnd DATETIME = '20140618 00:00:00.000'

  DECLARE @predRange FLOAT = DATEDIFF(ms, @predStart, @predEnd)
  DECLARE @stepRange FLOAT = DATEDIFF(ms, @stepStart, @stepEnd)

  DECLARE @F FLOAT = @predRange / @stepRange;

  DECLARE @avg_range_rows FLOAT = 100.3333
  DECLARE @distinct_range_rows INT = 3
  DECLARE @EQ_ROWS INT = 0

  SELECT @F AS 'F'

  --for new cardinality estimator

  SELECT @EQ_ROWS + @avg_range_rows * (@F * (@distinct_range_rows - 1) + 1) AS [new_card]

After calculating, I got the following results:

According to the formula, it turned out 150.5, but the optimizer estimates the predicate at 225.75 rows, and if you change the upper border of the predicate to ‘20140617’, the optimizer will already evaluate 250.833 rows, while using the formula we get only 200.6666 rows.

Please tell me, how does Cardinality Estimator evaluate in this case, maybe I made a mistake somewhere in my understanding of the quoted formulas?


texts: Using the special Dual table to read sequences

texts:
It there a performance impact when using the dual table to read a sequence' nextval over an direct insert?

I'm currently optimizing the performance in a large IT system and what I've found is that almost all sequence.nextval are read in the following way:

SELECT SOME_SQ.NEXTVAL INTO someID FROM DUAL;
insert into .... (someId)

I'm wondering whether the performance might suffer when using the way as described above over a standard:

insert into .... (some_sq.nexval, ...)

Thanks


texts: Does SELECT * create additional overhead even when you want all columns?

texts:
I have read that SELECT * is typically undesirable in that it creates additional overhead. Is this the case if you want to retrieve all columns?

I have a extremely badly performing queries, which is made up of nested 'SELECT *' queries. In all cases I do want all the columns returned to me though...


texts:
Hello, all! I have several needs that I'm attempting to accomplish with my database implementation.

List number of wins / losses for a particular user
Keep track of the amount of coins a user has
Allow user to purchase items from a shop
List profitability from all gambling-based games

For my use case, I have several games the end user can play. The "Fun" category of games does not award 'coins'; however, I want users to be able to list how many they've won. All the gambling-based games allow players to earn / lose their 'coins' when playing.

Fun Games:

Rock, Paper, Scissors
Connect Four
TicTacToe

Gambling Games

Slot Machine (Slots)
Black Jack
HiLo
Coin Flip

After doing some research online, I have implemented a 'match' table to keep track of all the matches between players. With two player game sessions, two rows will be created. If I want to know how many wins a player has, I can use a sum(wins) from the match table. Additionally, from the 'gambling' match table, I can run a similar query to pull the profitability loss / gain. Is there a better way of doing this?
Additionally, if a user profited from a gambling match - I'm assuming I'd need to perform two operations:

Add new row in GamblingMatch
Update amount of coins a player has

At this moment, I'm not sure of a way to do that in one operation.


texts: What can I do to monitor "out of shared memory" issues?

texts:
I'm new to database administration and I would really like some advice on what tools I could use or what I could do to monitor "out of shared memory" issues.

I've been seeing these "out of shared memory" messages when I've been running rspec which runs tests for my application. I've also seen messages in the browser when I run my rails app  that I "might need to increase max_locks_per_transaction."

I think it would be better to find out exactly what is going on first either on the driver level or the PostgreSQL level.

My specs are:

PostgreSQL 9.4.5 on x86_64-apple-darwin15.0.0, compiled by Apple LLVM version 7.0.0 (clang-700.1.76), 64-bit

I have a Macbook Pro running OSX El Capitan 10.11.5

I'm using the gem pg version 0.18.4 in my Ruby-on-Rails application.


texts:
Is there a way to dispatch stored procedure invocation on particular shard, based on sharding key (passed as procedure argument or somehow separately)?

For example we have a table, sharded between 3 nodes using HASH sharding strategy. All rows with key A are stored on node 1. I'd like to be sure that when I run procedure(A), it applies on node1.

We are going to use Mysql Fabric tool, but other options considered as well.


texts: MSSQL - Retrieving the top 5 time series within a query

texts:
I'm utilizing the Grafana site with MSSQL for my querying of time series and attempting to write 5 time series to a graph, my current query is
SELECT FLOOR(DATEDIFF(second, '1970-01-01', date)/1800)*1800 as time,
       name as metric,
       SUM(num/1.201) as value
FROM table
WHERE CAST(date AS DATE) BETWEEN '2020-05-27T19:13:49Z'
                             AND '2020-05-29T19:13:49Z'
GROUP BY FLOOR(DATEDIFF(second, '1970-01-01', date)/1800)*1800,
         name
ORDER BY 1,
         SUM(num) DESC

and this successfully returns multiple time series for my graph, but I have over 100 names/series and only want the top 5 series (by sum) to be displayed, any insight would be appreciated for ways of accomplishing this or just routes to explore as possibilities.
Thank you in advance!


texts:
Say I have a product table with the following columns:

product
--------
product_id
name

And I have another table to record the transaction (IN/OUT) for each product

product_transaction
--------------------
transaction_id
transaction_date
product_id
type
quantity

I want to have a page that lists that products with its details including the current quantity. At the same time, I also want a page that lists all transactions within a day (or a range of dates) that also include the running total for the current transaction.

My question is, shall I store the running total in the product_transaction table and the current quantity in the product table. Or is it quick enough to just compute them on the fly?


texts: MSSQL Server Upgrade, go for latest or version before that

texts:
A dilemma probably faced by quite a few DBAs. Does one upgrade to the latest version, in this case SQL SERVER 2016, or play it safe and upgrade to 2014, which hopefully has most bug fixes in place by now?

I will only be upgrading in Standard Edition and from what I've read there isn't much between 14 & 16 in terms of functionality, enterprise is a different story.
The only things of interest to me at the moment in 2016 are probably the query store features and possibly the tabular BI stuff.

I have read 2016 does use fewer resources and depending on the tasks it executes faster, which is obviously a big bonus.

At the moment I am leaning towards 2016 as opportunities to upgrade are few and far between, just worried about potential bugs and applying them in 24/7 up time environment with no maintenance window.

Would be great to hear if anyone else has faced the same/similar upgrade path, and ultimately what decision they took.

I will probably get a down vote as there is no right answer...but its a question that must be asked a lot..


texts: how can I recover windows xampp data in ubuntu xampp by file transfer

texts:
My windows got crashed but i was able to retrieve the xampp folder in which i want to recover my data in ubuntu xampp.
I have tried to solve this problem by copying the following files from the windows xampp data folder into /opt/lampp/var/mysql and granted all directory the permission of 700 and all files in current working directory and subfolders the permission of 660
ibdata1
ib_logfile0
ib_logfile1
[database] containing *.frm and *.ibd

I am still unable to recover my data even Mysql refuse to run when I tried to restart it. Please I need help on how to solve this problem.


texts: Fixing parameter-sniffing of an embedded query via Recompile

texts:
One of my multi-step queries makes use of optional filters.  Rather than using dynamic sql or if statements, I am attempting to use option(recompile) to make the filtered step choose appropriate indexes regardless of which optional filters are provided.  This did not work once I tried to store the results of the sub-expression.

I was able to reproduce this in SSMS as follows:

declare @UID bigint = 100

--Query A: Uses index on UID
select count(1) from Entries
where (@UID is null or UID = @UID)
option(recompile);

--Query B: Does an index scan (much slower)
select @cnt = count(1) from Entries
where (@UID is null or UID = @UID)
option(recompile);

--Query C: Uses index on UID
select @cnt = count(1) from Entries
where (UID = @UID);

My goal is to make query B use an index on UID if @UID is not null.  I could accomplish this by conditionally replacing it with Query C, but my preference is to avoid that, as it strikes me as overly verbose.  I can't use Query A, since I want to store the intermediate result for use in the next query within a stored procedure.

I did try selecting into a temp table and I tried using nullif, but these approaches still did a scan rather than a seek.


texts: Join two tables where the other table has no equivalent value in the other table using MySQL

texts:
I want to join two tables where some rows does not have a value in the other table.

This is the first table:

ID      TDNO    lotNO   area    VALUE      DATE    MEMO
------  ------  ------  ------  ---------  ------  --------
100     1       111     100     100.00     2015    ASDASD
101     2       222     100     100.00     2015    SDFSDF
102     3       333     200     100.00     2015    TEST
103     4       444     200     200.00     2015    FOO
104     5       555     330     200.00     2015    BAR
105     100     100     100     11,000.00  2016    100

This is the second table:

tdno    hiid    sequence_no
------  ------  -------------
100     qwerty              0
A) 1    qwerty              1
2       qwerty              2
3       qwerty              3
4       qwerty              4
5       qwerty              5

What I want is that I want to join the two tables but as you can see there is a row that has no value in the other table. How to join this?

This is my query so far:

SELECT
  ht.ID,
  ht.TDNO,
  ht.lotNO,
  ht.area,
  FORMAT(ht.assess_value, 2) AS 'VALUE',
  ht.EFF AS 'DATE',
  ht.memoranda AS 'MEMO',
  ts.hiid,
  ts.sequence_no
FROM
  history_table ht
  RIGHT JOIN td_sequence ts
    ON ts.`tdno` = ht.`TDNO`
WHERE hiid = 'qwerty'
  AND sequence_no >= '0'
  ORDER BY sequence_no ASC

And the result is like this:

ID      TDNO    lotNO   area    VALUE      DATE    MEMO    hiid    sequence_no
------  ------  ------  ------  ---------  ------  ------  ------  -------------
105     100     100     100     11,000.00  100     100     qwerty              0
(NULL)  (NULL)  (NULL)  (NULL)  (NULL)     (NULL)  (NULL)  qwerty              1
101     2       222     100     100.00     2015    SDFSDF  qwerty              2
102     3       333     200     100.00     2015    TEST    qwerty              3
103     4       444     200     200.00     2015    FOO     qwerty              4
104     5       555     330     200.00     2015    BAR     qwerty              5

What I want is like this:

ID      TDNO    lotNO   area    VALUE      DATE    MEMO    hiid    sequence_no
------  ------  ------  ------  ---------  ------  ------  ------  -------------
105     100     100     100     11,000.00  100     100     qwerty              0
100     A) 1    111     100     100.00     2015    ASDASD  qwerty              1
101     2       222     100     100.00     2015    SDFSDF  qwerty              2
102     3       333     200     100.00     2015    TEST    qwerty              3
103     4       444     200     200.00     2015    FOO     qwerty              4
104     5       555     330     200.00     2015    BAR     qwerty              5

How to achieve this?


texts:
I have the following tables:

players: Columns: name, school_name, gender

double: Columns: player_name_fkey, school_name_fkey, category, tournament_number, score

My entries in the simple table will be similar to this. Each line is the score by a player at a specific tournament number.

Joe, Harvard, Junior, 1, 40
Joe, Harvard, Junior, 2, 30
Joe, Harvard, Junior, 3, 60
Anne, MIT, Senior, 1, 45
Anne, MIT, Senior, 2, 55
Anne, MIT, Senior, 3, 20 etc.

What I am looking to do is to get is, for each player, its total score over all tournaments. I then intent to select the best five players per school (which is why the partition command).

I have the following query:

SELECT player_name_fkey,
       school_name_fkey,
       sum_scores,
       rank() OVER (PARTITION BY school_name_fkey ORDER BY sum_scores DESC) AS scorerank
FROM (
  SELECT player_name_fkey,
         school_name_fkey,
         score,
         sum(score) AS sum_scores
  FROM double
    LEFT JOIN players ON players.name = double.player_name_fkey
  WHERE category = 'Benjamin'
    AND gender = 'Masculin'
    AND tournament_number >= 2
  GROUP BY player_name_fkey,
           school_name_fkey,
           score
) t1
GROUP BY t1.player_name_fkey,
         t1.school_name_fkey,
         t1.sum_scores;

I do get the correct sum for players, but unfortunately, I get an extra duplicate row for each player with a value of zero and I have no idea why.

Here is the code: http://rextester.com/XRRX92434

Thanks in advance for any help!

Benoit


texts: Copy of a database with an existing database with schema and data in sql server

texts:
I have two databases one is having master kind of tables and other will have transaction data.
I should create a new database with a structure of an existing database to my clients. Here is the scenario..

If the particular db will comes under transaction type then structure of an existing db should copy
If the particular db will comes under master type then structure of an existing db along with data should copy

i.e Transaction--> Only Schema
       Master--> Schema+Data

I tried with Generate script, Is there any way to automate this process instead of manually generating script for both schema and Schema+Data for every copy of a new db.

Please suggest.


texts:
I have had my cup of coffee, but cannot come up with a good solution for the following problem - so I am here to pick your brain. ;-)

Here is what I am trying to accomplish (for PostgreSQL):

Got a table with logged communications related to products.
The table contains an ID (PK, serial), date/time when inserted, a product ID, an error code field (and some other fields not relevant for now)
Rows get validated upon reception, and all rows are stored.
Rows are either accepted or rejected (error code field).
When accepted, fine, the rows get processed (as a 'step 2' - not relevant for now).
However, when rejected the sender gets an error message and is requested to correct the data and resend.
So, when the corrected data is received again and is validated and accepted, we should have a product row that got rejected and a product row that got accepted.
We have learned that not all senders check these error message and resend their (corrected) data.

So, what I am looking for are these rows for product that were rejected but never sent again correctly - it may happen that rows get send multiple times and rejected multiple times by our validation process.

I have been at it for a couple of hours, but so far without much luck.
What SQL trick could I pull out of the hat to find these rows?


texts: Restore Database from Database with Different Name

texts: I have 2 databases, DB1 and DB2.  DB1 has production/live data.  A script runs daily (takes about 3 hours) to fill DB2.  The goal is to restore DB1 with DB2 so that DB1 is down for as little time as possible.  I've restored databases before but never from a differently named one.  How can I do this?


texts: SSRS shared data sources and shared schedule

texts: I am cleaning up our existing SSRS (report server) and would like a few suggestions and best practices in terms how it should be used. Currently each report has a connection and schedule. Would it be a good practice if I created a few shared schedules? What would be the impacts in terms of performance and resources? Also, would there be any impacts if I were to create a Data Source is SSRS and use that particular shared data source in reports? Also, if lets say 10 reports are scheduled to run at 1:00PM , would they queue or would they execute all at once?


texts: SQL Server domain account login and user

texts:
What is the difference between

adding a domain account login to an SQL Server (2008 R2) instance then add a corresponding user to a database
adding a domain account user to a database without adding as a login first

Is there any advantage, benefits, etc of one over the other.  I notice when the latter strategy is used (i.e. user only), sp_helpuser has a NULL LoginName

When the former is used (i.e. login and user) apart from say being able to assign an instance level role (as opposed to simply a database role) is there any problem with just adding a user without login (the latter)


texts: admin user can not login to other databases after upgrading to mongodb 2.4

texts:
I had a mongodb 2.2 database with auth enabled. but after upgrading to 2.4 my admin user can not login to any database other than admin database. I removed it from system.users then created another admin user with:

db.addUser( { user: "admin", pwd: "123456", roles: [ "userAdminAnyDatabase" ] } )

But again I can not login to any database except admin database. Can anyone help?


texts: Sch-M WAIT blocks Sch-S in SQL Server 2014 but not SQL Server 2008 R2?

texts:
We recently migrated our production instances from SQL 2008 R2 to brand new SQL 2014 servers. Here is an interesting scenario that we uncovered with our usage of Service Broker. Consider a database with Broker Enabled = true with MyService and MyQueue. Poison message handling is disabled on this queue. There are least 2 active conversations with messages in the queue.

In one process (SPID 100) execute:

BEGIN TRANSACTION;
DECLARE @conversation_group_id UNIQUEIDENTIFIER;
RECEIVE TOP (1) @conversation_group_id = conversation_handle FROM MyQueue;

Note that we leave the transaction open. Imagine that it's a .NET program that's waiting a long time on some external resource. Via sys.dm_tran_locks we see that this SPID has been granted an IX lock on the queue.

| type   | resource_id | mode | status | spid |
| OBJECT | 277576027   | IX   | GRANT  | 100  |

In a separate process (SPID 101) execute five times:

BEGIN TRANSACTION;
DECLARE @conversation_group_id UNIQUEIDENTIFIER;
RECEIVE TOP (1) @conversation_group_id = conversation_handle FROM MyQueue;
ROLLBACK TRANSACTION;

The key here is that we are rolling back the transaction five times. This triggers the built in Poison Message Handling background logic. While the queue does not get disabled (because it's configured to not disable), a background task is still trying to do work and fire a broker_queue_disabled event. So now if we query sys.dm_tran_locks again we'll see a different SPID (associated with BRKR TASK) waiting on an Sch-M lock.

| type   | resource_id | mode  | status | spid |
| OBJECT | 277576027   | IX    | GRANT  | 100  |
| OBJECT | 277576027   | Sch-M | WAIT   | 36   |

So far, everything makes sense.

Finally, on a different process (SPID 102), attempt to SEND to a Service using that Queue:

BEGIN TRANSACTION;
DECLARE @ch uniqueidentifier;
BEGIN DIALOG @ch FROM SERVICE [MyService] TO SERVICE 'MyService';
SEND ON CONVERSATION @ch ('HELLO WORLD');

The SEND command is blocked. If we look again at sys.dm_tran_locks we see that this process is waiting on a Sch-S lock. Executing sp_who2 we find that SPID 102 is blocked by SPID 36.

| type   | resource_id | mode  | status | spid |
| OBJECT | 277576027   | IX    | GRANT  | 100  |
| OBJECT | 277576027   | Sch-M | WAIT   | 36   |
| OBJECT | 277576027   | Sch-S | WAIT   | 102  |

Why does a Sch-S lock wait on a Sch-M lock that is also waiting?

This behavior is completely different in SQL 2008 R2! Using this exact same scenario, running on our yet-to-be-decommissioned 2008R2 instances, the final batch including the SEND command does not get blocked by the waiting Sch-M lock.

Has locking behavior changed in SQL 2012 or 2014? Is there perhaps some database or server setting which might affect this locking behavior?


texts: Data Source connects but linked server cannot initialize data source object in MS SQL Server

texts:
I'm trying to create a linked server to an instance of MySQL. I've created a System DSN to the instance and that passes the connection test, but when I try to create a linked server using that data source, I get an error (specifically Cannot initialize data source object of OLE DB provider MSDASQL for linked server "MyServerName") The system error is 182, MS Sql Server error 7303. Screen shot below:

Below is a screen shot of the general settings (TEST_MYSQL is the name of the data source I've previously and successfully configured and tested):

I'm using the "use this security context" with a username and password that work
in the data source name. Below is a screen shot of the server options.

I'm not sure what I'm missing or doing wrong, but this isn't working. I would assume that something is configured wrong, but the data source connects successfully using the same creds. Is there something missing that would make the data source connect fine but prevent the linked server using that data source from doing so? Please advise, thanks!


texts: Oracle Data Guard - redo logs not in chronological order?

texts:
The disadvantages section of the Wikipedia entry for Oracle Data Guard says:

  If the network link connecting primary and standby is over-subscribed, the redo logs are not applied in chronological order

Is this true? I can't find a reference to this in the Oracle documentation.


texts: mysql join by 2 ways - how it works? - which gives performance difference

texts:
I had 2 different ways to query the content which showed performance difference when executed. The 1st way is

EXPLAIN select SOME_COLUMNS
from
( select *
  from A
  where CONDITION
) p
inner join
( select *
  from B
)st
on p.id = st.id;

and the output of this query returned:

"id"   "select_type"   "table"    "type"   "possible_keys"   "key"    "key_len"   "ref"   "rows"   "Extra"

 1       PRIMARY     derived3  ALL       NULL                 NULL    NULL    NULL      25607     "   "
 1        PRIMARY    derived2  ALL        NULL                NULL     NULL    NULL      21037   Using where; Using join buffer
 3        DERIVED             A    ALL          NULL               NULL      NULL    NULL      23202    "   "
 2        DERIVED             B    ref        IDX_A_TYPE_ID   IDX_A_ID  98      "   "        12411  Using where

The other way is

EXPALIN SELECT SOME_COLUMNS
FROM A p, B s
WHERE p.id = s.id
AND p.CONDITION;

The output of this looks like this:

id       select_type       table       type       possible_keys       key     key_len    ref     rows   Extra
 1       SIMPLE           p            ref       PRIMARY,IDX_A_TYPE_ID      IDX_A_TYPE_ID    98    const    12411    Using where
 1      SIMPLE            s           ref             PRIMARY       PRIMARY     4   local_db.p.entity_id    1

Why is there such a difference in the query execution plan between the 2 and number of rows being fetched in each step of query execution is more in the 1st approach? Please explain it.


texts: Intra-Query Parallelism Deadlock Resolution

texts:
I want to know how SQL Server handles intra-query parallelism deadlocks, and whether it kills one of the threads involved in the deadlock?

Will there be a change in the result set when this deadlock occurs? Or this just affects performance?


texts:
I was asked this question in an interview and I could not get my mind around it, and now it is bothering me a lot!

If you have a table CANADA(PROVINCE, CITY, POPULATION).
- Population is correlated with the population of one city.
- There are multiple cities in a province.

Question:
Write a query that would list the total population of each Province.

I am a beginner when it comes to SQL related questions. Knoweldge of SQL was not in my job description, but they just wanted to know if I could solve it.

I came up with this:
SELECT PROVINCE, POPULATION
WHERE CANADA
GROUP BY PROVINCE

However I did not know how to move forward with this. I would like to know of I am going the right direction please!


texts: SQL Server 2012 Express : How do you limit a database's size and retention policy?

texts:
I don't work with databases very often and I want to make sure I am going about this the right way.

I want to configure a database's file size to not exceed 9 gigs. At the point the database reaches this file size limit I would like it to purge 25% percent of its oldest records to accommodate new records.

Is this something I could/should configure on the database server itself, like a one time maximum setting config, or does this have to be handled by the program that is updating the database (it would check the capacity and tell it when to purge records).

I'm not a DBA so I'm unsure of how this is usually handled.


texts: Report Builder Indicator showing incorrect symbol

texts:
I have a report on which I am using about a dozen indicators as a check-boxes.  The data source for the report is a SharePoint list.  About half of the check-boxes are displaying correctly. The other half, instead, are showing a capital A.  The query I am using is:

=IIF(Fields!Quality_Record.Value="yes",1,0)

The indicator state is set up so that a value of 0 shows no icon and a value of 1 shows the check symbol. I have even deleted this indicator and replaced it with a copy of one of the working indicators and it still will only show a capital A regardless of the value of the field.


texts:
I am creating a small booking application with Laravel. It lists some centers. Each center has its own unique rooms. Each room may be booked several times (with dates). From my application, I post the 'from' and 'to' dates. I need to add the date range as booking dates of a particular room. Remember, a room can be booked more than once, but to a limited number of times. I need this because I want to mark the booked days with different colors in a calendar in the front-end.

Just in case you want to know, I'm more concerned about the booked dates more than everything else.

Thanks.

UPDATE:

I want advice on how to setup the database tables and columns. Then, how to set the booked dates of a room. Is it enough if I set a Start and End date to the booking, or do I need a row for each day that is booked?

Thanks.


texts: I have 2 devices, a server computer installed with SQL Server 2012, and a raspberry pi with MySQL installed. I have data in MySQL tables. How can I connect SQL Server 2012 to MySQL so I can save that data?


texts: How to explain/introspect what locks a DDL operation will need (Postgres)

texts:
I would like to have some way of inspecting what locks a DDL operation will need to acquire.

Preferably something generic enough that I don't need to personally know/encode knowledge of all of the Postgres locking rules.

Something like EXPLAIN but specific to DDL operations.

I don't want to run the command and then look at any locks that are blocking... I want to know in advance what locks the DDL operation will need to acquire (e.g. ACCESS EXCLUSIVE on tables x, y, z), taking into account existing foreign key constraints etc.


texts: archive_command failed causing master to run out of disk space. Is there a command that prioritizes master's health

texts:
I had replication set up with a simple archive_command in postgresql.conf:

archive_command = 'rsync -a %p _postgresql@serverip:/var/postgresql/wal_archive/%f'

Which worked great for 2+ years until the replica went down.  After the replica went down, the archive command failed repeatedly so pg_xlog files on master were never deleted and master ran out of disk space.

In my use case the replica is nice to have but not that important.  However ensuring master is always available is very important, so I'd like to use an archive command that will sacrifice the replica before problems arise on master.

I currently went to the other extreme and changed the archive_command to:

archive_command = 'rsync -a %p _postgresql@serverip:/var/postgresql/wal_archive/%f || true'

But this doesn't seem ideal either since even a few seconds of network outage could cause replication to fail.

Ideally I'd like to change the archive_command to something that will try to copy the WAL file to the replica a certain number of times, and if that fails, give up and remove the pg_xlog file.  Any suggestions for a new archive_command?

(postgres 10.5)


texts: SQLDeveloper: Unable to desc a table that exists

texts:
I have a table, let's call it MYTABLE.

In SQLDeveloper, I can see the data in the table using:

SELECT * FROM MYTABLE

However, when I do DESC MYTABLE, it gives me an error:

  ERROR: object MYTABLE does not exist.

If I try the same thing from sqlplus, there is no error and I get a listing of the columns in the table.

I checked dba_all_tables and there is a row for the table.

select * from dba_all_tables where table_name = 'MYTABLE';

returns 1 row as expected.

Any ideas as to what I'm missing?

The command works fine with a different table (MYSCHEMA.MYTABLE2), so it's likely not a sql developer bug.

The same table exists in several schemas:

select owner, object_type, count(*) from dba_objects where object_name='MYTABLE' and owner='MYSCHEMA' group by owner, object_type;

MYSCHEMA    TABLE SUBPARTITION  128
MYSCHEMA    TABLE PARTITION 16
MYSCHEMA    TABLE   1

Unfortunately, I can't provide a set of steps to reproduce this independently because these tables already exist in the schema I'm working on. My best guess right now is that something went wrong during the table creation long ago, and I can't go back and look at the source code that was executed back then because of a SCCS change in the company.

MYTABLE is not a synonym or mview.


texts: Delete rows in a table based on a table value parameter

texts:
I have a table A and a user defined table type on which a table value parameter (tvp) tvpA is based.  Both have the same column structure except that tvpA is not defined with any constraint.

What I'd like to do is to delete rows in A that are not in tvpA based on one of their field called ID.

How can I do that ?


texts: Different MySQL Datafile Sizes After Restoration

texts:
I'm quite new in database administration. The database architecture of my current company is master-slave replication. We are using MySQL version 5.0.86.

Some weeks ago, we upgraded the company's main app. This upgrade added a column to all the tables of its database. Before the upgrade, the size of our ibdata was ~3.1GiB. After the upgrade, it is ~5.2GiB.

Today, I've done a full backup-restoration test with mysqldump. After restoration, ibdata size is ~3.6 GiB. Standard selects show the same data in restoration as in original database.

I've checked the data size through a select to information_schema (CUSTODIA is the name of our main app):

select SUM(DATA_LENGTH+INDEX_LENGTH) from TABLES where TABLE_SCHEMA='CUSTODIA';

this is the result in master database:

+-------------------------------+
| SUM(DATA_LENGTH+INDEX_LENGTH) |
+-------------------------------+
|                    5683345068 |
+-------------------------------+

this is the result in restored database:

+-------------------------------+
| SUM(DATA_LENGTH+INDEX_LENGTH) |
+-------------------------------+
|                    3735748608 |
+-------------------------------+

My questions:

Why is there this difference between original and restored database size?
Is it safe to asume that restored database is OK, although this difference in size?
How does MySQL calculate data_length? Is it an estimate?
Can I safely reduce production's ibdata file size to 3.6GiB with no down-time?

Any thoughts about this will be kindly appreciated. Thank you.


texts:
In Oracle there is an important note in the explain plain where it says filter a.col1=... this one letting you know col1 is first retrieved from the disk and only then filter is been done. This important piece of information let you know the exact column been used within a specific index and which are filter after and may be good candidate for index.
In MySQL explain result we see something like:
+----+-------------+-------+------------+--------+--------------------+---------+-------+--------+----------+------------------------------------+
| id | select_type | table | partitions | type   | key                | key_len | ref   | rows   | filtered | Extra                              |
+----+-------------+-------+------------+--------+--------------------+---------+-------+--------+----------+------------------------------------+
|  1 | SIMPLE      | SD    | NULL       | range  | ix_store_composite | 64      | NULL  | 252978 |    60.00 | Using index condition; Using where |
+----+-------------+-------+------------+--------+--------------------+---------+-------+--------+----------+------------------------------------+

What I would like to know which of the columns data are coming from the index and which are been filtered after (the using where).
I couldn't find any option to get additional detail on the explain


texts: Postgres: Are secondary indexes included in ACID?

texts:
Are non-unique indexes / indices covered under the Consistency clause in aCid? (same for other attributes of an index that do not place constraints on the data) I am seeing certain performance issues (benefits, actually) in Postgres that make me wonder if they are.

Given that indexes / indices are not first-class objects (i.e. you can't access them directly in Postgres, nor can you request their use), I see no reason at all why Postgres would be REQUIRED to support this. I can find no definition of ACID that says "indexes have to absolutely complete and not be hacked up before the transaction can finish".

Under certain conditions that do not place restrictions on the insert (such as the index not being unique), the index could essentially be "invalidated" (i.e. "don't use it until I'm finished reindexing"), or flags can be set that says "the index does not cover the following ranges".

If Postgres played this trick, the copy from operator could be made exceedingly swift (which is what I am seeing), similarly for massive insert counts in a transaction.

I'm not just making this up...

While Redshift is a bad example, Amazon weasels out of Consistency by playing tricks with how it stores the (one and only) sort key (essentially a primary index-ish construct in Redshift). Until one performs a vacuum command, the primary key just keeps getting worse and worse and your database starts becoming a black hole: queries go in, but no results come out.

Clearly, an internalized vacuum regimen would prevent the Redshift silliness that often occurs during mass imports.


texts: Compatibility of queries written in SQL Server 2012 with SQL Server 2016

texts: Can queries designed and written for SQL Server 2012 be used as is on SQL Server 2016. We are planning an upgrade of SQL Server and want minimal manual intervention in order to transition our work


texts:
I have a table users(id and name) and order(cost and user: FK to users).

users.name users.id
bob               1
alice             1

and

orders.user orders.cost
          1          15
          1           5
          2           4
          1           4
          1           3
          1           3
          2           3

Here is a query which joints users and orders and sorts by order cost.

users.name users.id orders.user orders.cost
bob               1           1          15
alice             1           1           5
bob               2           2           4
alice             1           1           4
alice             1           1           3
alice             1           1           3
bob               2           2           3

If I were to do a distinct on user (in Postgresql), I would get

users.name    cost
bob              15
alice             5

I actually want the counts, while still being sorted on highest order cost/

users.name    count
bob               3
alice             4


texts: How to properly structure multiple user types?

texts:
So i'm building a database for an app/website. It's essentially a e-commerce marketplace/platform where a brand can sign up to upload their products and customers can also then sign up to purchase any products from any various brands.
A brand and or employee cannot be a customer
A customer cannot be a brand
This is the structure I have so far, but it seems to me that from this diagram it implies that a user can be a customer, brand and brand employee at the same time.
Is there a better optimal structure for this?


texts: Backup/restore using OPENDATASOURCE OR OPENROWSET

texts: Has anyone ever successfully taken a backup or restore to another server using OPENDATASOURCE/OPENROWSET? I am not able to use linked servers so I am exploring this idea.


texts: mysql replication goes out of sync for some tables

texts:
We are running mysql 5.1.61 on redhat systems and have the following setup

One master and four slaves replicating from the master, we recently added a new slave for replication and over a few days we have started noticing that on the newly added slave some tables ( not all ) loose some records , this happens only on this slave and it is not regular , over a period of 3 weeks this issue seems to have happened on 5-7 days .

We use statement based replication. I am not sure why this happens on only one slave. There seems to be no error in the mysql error logs. The only difference between the old slaves and the new slave is that the new slave has a slightly lower ram than the other ones but the new slave is not being used for anything right now.

Is there a way to trouble shoot this issue to see why this happens on only one slave ?. Could it be network related or anything else ? Any pointers on where to start looking at ?

Here is the memory info
Old slave

            total       used       free
 Mem:      24731204   24641572      89632

New slave

Mem:      16431192   10112880    6318312


texts: Meta Database for a multi-tenant multi-shard environment

texts:
I’ve spent quite a bit of time researching sharding of a multi-tenant database (for a SAAS application).  I wasn’t able to find much information on handling the Meta Database, containing connections and common data for the tenant databases. A simplified model of what I'm working on would look like this: several servers, 2 or more shards with identical structure on each server, each shard containing a few databases with data for a subset of the tenants (example: Server 1; shards 1; tenants 1- 500; databases A, B, C with data for tenant 1-500).

I’ve attached a simplified model for a single server.

My question is what approach should I use for the Meta Database once the shards are distributed on more than 1 server? It is straight forward(and my proof of concept worked well) having 1 Meta Database connecting the shards on only 1 server(which will be the case in the beginning), but the implementation would be different for multiple servers with multiple shards each, since the Meta Database would need to contain the connection to each tenant.

This is a relatively new territory for me so your thoughts and any guidelines would be greatly appreciated.

Thank you!

Vilyana


texts:
I know this is kind of a beginner question.  I am maintaining legacy code and data and just have to deal with the way things are  But if you were to design a new database from the start - is there any legitimate reason to create two tables with a one-to-many relationship in Microsoft Sql and not use a Foreign Key?

We currently are not in some tables and the reasons to not implement them now make sense , but none of the reasons are really sufficient to explain why the keys were not implemented originally


texts: How to truncate an SSAS Tabular partition

texts:
I have a tabular cube with a large partitioned table.

I'd like to truncate some of the partitions (freeing the memory space), but retain the metadata, so that I can quickly  rebuild them if required at a later time, without having to recreate the partition.

Is this possible? If so, how can it be done?


texts: Is there something to rescue from an Oracle Data Modeler design just with the 'logical' folder?

texts:
First of all, I even don't know how naive is the question, we start studying this not long ago.

A friend of mine was working on Data Modeler, he saved the design and he only got the "logical" folder, without the other ones nor the .dmd file, is still possible to open this with Data Modeler and rescue something or all the work was lost?


texts: How do I swap tables atomically in Oracle?

texts:
How to do I swap two tables in an atomic fashion in Oracle?

I would like to perform the equivalent of:

ALTER TABLE foo RENAME foo_tmp;
ALTER TABLE foo_new RENAME foo;

but what happens if a query needs table foo in between those two lines when there is no table foo? Some lock would be required.

P.S. For a MySQL version of the question, see this question.


texts: Can IMPLICIT_TRANSACTIONS set on database level?

texts:
When I have to investigate problems caused by IMPLICIT_TRANSACTIONS ON, can I focus on Client tools setting IMPLICIT_TRANSACTIONS ON or ANSI_DEFAULTS ON or can this be set for the whole database affecting all clients?

I can avoid the problem by changing my standard script header to

SET ANSI_DEFAULTS ON
SET IMPLICIT_TRANSACTIONS  OFF

but at which levels session, database, server can this setting be influenced?

Edit:

I found this in msdn

it seems to be possible to set this on server level.

In the Server Properties dialog (Connections Page) I found the option changed and scripted it:

implicit transactions on:

EXEC sys.sp_configure N'user options', N'2'
GO
RECONFIGURE WITH OVERRIDE
GO

implicit transactions off:

EXEC sys.sp_configure N'user options', N'0'
GO
RECONFIGURE WITH OVERRIDE
GO

But executing

exec('dbcc useroptions')

in SSMS didn't reflect the change. The server setting seems to be somehow overwritten for such connections.

Now I have to repose the question,

are there clients which do not overwrite the server setting and can crash when the server setting is changed.


texts: "Cut" records from one table into another in one oracle sql statement

texts:
I have a very big table T1 and i would like to copy every 15000 records to T2.
After that I want to delete these same records.
I want a fast and efficient way.
I tried this but it wouldn't work :

merge into T2 T
using (select col1,col2 from T1 where rownum<15000) m
on (1=1)
when matched then
insert values (m.col1,m.col2)
delete where 1=1;

It's giving me a syntax error. Any ideas??
Please help.
Thanks


texts: One database listening on two database names

texts:
I know this is a sort of odd request. I myself have never had to even think about a scenario like this until today. I'll go straight to the point to make the question easy to read. I suspect the right answer will involve more of "thinking outside the box" than a hidden feature or capability that somehow I'm not aware of.

Here is the thing: I need a PowerBI dashboard to query my SQL Server, but the PowerBI dashboard will have a connection string that queries a database (let's call this database "DatabaseOne_Report") whereas the SQL Server will host a database with a different name (let's say "DatabaseOne").

So when the PowerBI dashboard runs a query to DatabaseOne_Report.dbo.MyTable I want SQL Server to provide data from DatabaseOne.dbo.Table (notice the "_Report" suffix is gone from the database name). Is that even possible somehow?

I can anticipate your first question would be why I am using a connection string with the wrong database name. The answer is that this dashboard connects to different environments and these environments host this database using either 1 of 2 different names for reasons that are beyond the scope of what I'm asking here. The bottom line is that we are trying to avoid re-writing the connection string of the PowerBI dashboard and hope we can use a single connection string to tackle both possible DB names.

I read a lot of questions in different forums to tackle aliases of 2 databases hosted in different boxes, but never seen anybody asking 2 databases hosted in the same box but with potentially two different names.

I thought about creating a empty "shell" of a database with the other name with nothing but synonyms, but that would be a lot of configuration... I was hoping to find something simpler.

Thanks!


texts:
I recently saw a statement like:

CREATE TABLE #tbl_AR_Data

I wanted to know what is the purpose of the symbol # (NUMBER SIGN, hash, pound sign) in this statement?


texts: Database Mirroring protocol TCP ports used. One default, one dynamic?

texts:
When running below query on the primary/secondary replica of a SQL Server Always On Availability Group™

SELECT DISTINCT local_tcp_port,protocol_type,num_reads,num_writes
FROM sys.dm_exec_connections
WHERE local_net_address is not null;

Two local tcp ports show up for the Database mirroring protocol, 5022 & 63420

Server Name local_tcp_port  protocol_type       num_reads   num_writes
ServerName  5022            Database Mirroring  102942598   5
ServerName  63420           Database Mirroring  5           89655349

The 5022 port is expected, as this is the one configured as the mirroring endpoint.

The other one seems to be a dynamic port, why and for what is this one used?

Could it have to do with the fact that one is showing a high number of reads (5022) and the other one showing a high number of writes (63420).

  Build version: 13.0.5264.1


texts: How to count max date row if current status column is 0 or 2?


texts:
I have 2 tables:

CREATE TABLE `users_ids_mapping` (
 `current_user_id` bigint(20) NOT NULL,
 `new_user_id` bigint(20) NOT NULL AUTO_INCREMENT,
 UNIQUE KEY `current_user_id` (`current_user_id`),
 KEY `new_user_id` (`new_user_id`)
) ENGINE=INNODB

CREATE TABLE `remote_users` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) NOT NULL DEFAULT -1,
    ..
  PRIMARY KEY (`id`),
  KEY `userId` (`userId`),
) ENGINE=InnoDB

The first table holds old user_id values and new ones, what I want to do is to update the second table remote_users user_id with the new values.

I do it by running 2 queries:

SELECT id FROM remote_users
ORDER BY id ASC
LIMIT {limit}
OFFSET {offset}

And when having the next ids, I run:

UPDATE remote_users r
JOIN users_ids_mapping m ON m.current_user_id = r.user_id
SET r.user_id = m.new_user_id
WHERE r.id IN({string.Join(",", ids)})

users_ids_mapping can reach to around 100k rows.

I am using batches of 10k rows. This worked for costumers with remote_users table of 10-100m rows, but now i ran it on a costumer with around 200m+ rows and its simply takes too much time.

I can see that in the beginning its pretty fast:

running batch number:1 with offset:10000 found:10000 rows, took:76ms

But when the offset is high it takes almost 3 mins to update 10k rows:

running batch number:13678 with offset:136780000 found:10000 rows, took:185949ms

Any suggestions on how I can speed up the process?


texts: What Other Information Is Stored in The Page Header

texts:
A SQL Server database page is defined as to be 8192 bytes in size.
There is some header information which is said to be 96 bytes in size.

And if you have ever tried to create a table with more than 8053 bytes of column definitions then you will hit the error message:

Creating or altering table 'Generated_Data_GUID' failed because the
minimum row size would be 8061, including 7 bytes of internal overhead.
This exceeds the maximum allowable table row size of 8060 bytes.

Following is an example table DDL:

CREATE TABLE [dbo].[Generated_Data_GUID](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [GUID] [uniqueidentifier] NOT NULL,
    [SEQGUID] [uniqueidentifier] NOT NULL,
    [Data1] [char](4000) NULL,
    [Data2] [char](4000) NULL,
    [Data3] [char](9) NULL,
    [EntryDate] [datetime2](7) NULL
) ON [PRIMARY]

With the above DDL if I change the column definition for column Data3 to be char(10), then I will hit the error message.

The byte sizes for each column type are as follows:

int               :  4 bytes
uniqueidentifiere : 16 bytes
char(n)           :  n bytes
datetime2(n)      :  6 bytes if n < 3
                     7 bytes if n = 3 or n = 4
                     8 bytes if n > 4

If we do some simple maths, then we end up with the following calculation:

Page Size         : 8192 bytes
                   -----------
Header            :   96 bytes -
Internal Overhead :    7 bytes -
Max Size          : 8053 bytes -
                   -----------
Missing Data      :   36 bytes
                   ===========

Question

What do these 36 bytes contain?

Reference Material

Pages and Extents Architecture Guide (Microsoft Docs)
Inside the Storage Engine: Anatomy of a page (SQLSkills.com)


texts: TSQL or SSIS for Direct Insert Speed Performance

texts:
I am moving data from Table A into Table B. Tables have the exact same DDL, table structure, and are straight inserts. We are Not transforming or modifying data.

If TSQL Server and SSIS have the same hardware specs for both cpu core and memory, and the two tables reside in a Same database, which tool is better performance wise? Table is 50 million rows, around 30GB.

Our performance testing shows T-sql; I would like the confirm what Microsoft or vendor recommends.

Purpose of question is to understand speed difference between Tsql and ssis.


texts:
I have been using MySQL as a windows service successfully on my windows 7 machine for perhaps a year.  It has always worked fine.  Here are the steps which I think caused it to no longer launch:

1.) My computer ran out of battery power and shut down automatically
    while the MySQL windows service was running.
2.) When I tried to restart the computer, it took a very long time
    to restore the windows session.  A dos prompt said it was
    restoring the windows session, and the fan was running very
    loudly.
3.) After perhaps 10 minutes of this, I turned off the computer
    forcibly by using the power switch.
4.) When I rebooted the computer, the dos prompt told me that
    the previous attempt to restore the windows session failed,
    and asked if I wanted to continue loading windows without
    restoring the previous session.  I said yes.
5.) When I tried to launch the MySQL windows service, I got the
    following dialog box indicating error 1067:

6.) I then tried to load MySQL from cmd.exe in order to see the
    error logs, and I got the following output which indicates
    that it cannot read/write:

How can I resolve this problem?

Here are the contents of my.ini:

[client]
no-beep
port=3306
[mysql]
default-character-set=utf8
[mysqld]
port=3306
datadir="C:/ProgramData/MySQL/MySQL Server 5.6/data\"
character-set-server=utf8
default-storage-engine=INNODB
sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
log-output=NONE
general-log=0
general_log_file="USER-PC.log"
slow-query-log=0
slow_query_log_file="USER-PC-slow.log"
long_query_time=10
log-error="USER-PC.err"
max_connections=100
query_cache_size=0
table_open_cache=2000
tmp_table_size=51M
thread_cache_size=9
myisam_max_sort_file_size=100G
myisam_sort_buffer_size=100M
key_buffer_size=8M
read_buffer_size=64K
read_rnd_buffer_size=256K
sort_buffer_size=256K
innodb_additional_mem_pool_size=100M
innodb_flush_log_at_trx_commit=1
innodb_log_buffer_size=4M
innodb_buffer_pool_size=305M
innodb_log_file_size=100M
innodb_thread_concurrency=9
innodb_autoextend_increment=64M
innodb_buffer_pool_instances=8
innodb_concurrency_tickets=5000
innodb_old_blocks_time=1000
innodb_open_files=300
innodb_stats_on_metadata=0
innodb_file_per_table=1
innodb_checksum_algorithm=0
back_log=70
flush_time=0
join_buffer_size=256K
max_allowed_packet=4M
max_connect_errors=100
open_files_limit=4110
query_cache_type=0
sort_buffer_size=256K
table_definition_cache=1400
binlog_row_event_max_size=8K
sync_master_info=10000
sync_relay_log=10000
sync_relay_log_info=10000

EDIT:

I then restarted cmd.exe as an administrator per Rolando's advice, and got the following results, which indicate that windows cannot find a previous session for the MySQL service.  Then cmd.exe remains hung up indefinitely without returning a user prompt:

SECOND EDIT:

I then did both of Rolando's suggestions from his edited response, and got the following results indicating that the log sequence numbers are out of sync with one another, which would seem to be the result of the failed restart of windows.

THIRD EDIT:

Below is the result of telling cmd.exe to echo the PATH variable:

FOURTH EDIT:

Here are results of running dir on the data directory:


texts: Best way to create searchable and sortable product database

texts:
I'm pretty good with coding but I'm a noob at database design.  This is a common problem but I didn't see an exact answer so I just want to confirm before I start coding everything.

I have a shopping site that lists a lot of products.  Currently I have 6,000 products but this could grow in the future to be quite large, maybe 100,000+ some day.  I list the products on the site by page and I let the user search and sort the products.

What is the most efficient way to design and query the database to search and sort products?  Do I simply create one big product table and then use queries like:

SELECT *
FROM productTable
WHERE searchCriteria
ORDER BY sortColumn
LIMIT basedOnPageNumber

Will this still work quickly when I have 100,000+ items? Is there a better strategy to use?


texts: postgresql 8.3 possible checkpoint and memory misconfiguration

texts:
I am using postgresql 8.3 in a read/write heavy application where data are being written to the database from a background process while they are also being read/written from a web application.

At peak hours, we notice performance problems in the database specifically high IO ( disk writes mostly, not reads).

I am suspecting checkpoints to be the problem. Our postgresql.conf file contains the following:

shared_buffers = 3000MB
work_mem = 256MB
maintenance_work_mem = 100MB
max_fsm_pages = 3448000
max_fsm_relations = 10000
wal_buffers = 1MB
checkpoint_segments = 32
checkpoint_completion_target = 0.9

while the rest are default.

Stats from the pg_stat_bgwriter table are the following:

checkpoints_timed:103881
checkpoints_requested:16
buffers_checkpoints:92105172
buffers_clean:83695499
max_written_clean:430652
buffers_backend: 33671343
buffers_alloc: 9001524439

Our system also has 20 GB of memory and a RAID1 disk array of two drives.

I turned today the checkpoint logging on though not during high traffic so I haven't been able to test it fully ( I will tomorrow though). In the log file I see a checkpoint every ~5 minutes. Together with the fact that there are about 100.000 checkpoints timed, this makes me believe that the server is performing a timed checkpoint every five minutes ( which is the default ? ). Since the requested checkpoints are only 16, it seems that it never reaches the 32 x 16 MB limit to initiate a checkpoint on request.

This leads me to the speculation that somehow our system is not properly configured. Should I increase the checkpoint timeout to like 30 minutes and the segments to an extend that will cause a checkpoint each time ~6 GB are filled ?

Aside from that, I am using redmine to monitor the machines memory usage and in the graph I see memory used to be about 500 - 800 mb while 6 GB are allocated to the swap and the rest to the file system cache. When issuing a TOP command the postgres processes are using about 4 GB of VIRT memory. Finally free -m indicates that only 500 mb are used the rest of the 20 GB being free.


texts: How do I find out *why* a user has certain effective permissions?

texts:
I know that I can query effective permissions by using sys.fn_my_permissions:

USE myDatabase;
SELECT * FROM fn_my_permissions('dbo.myTable', 'OBJECT')

 entity_name | subentity_name | permission_name
------------------------------------------------
 dbo.myTable |                | SELECT
 dbo.myTable |                | UPDATE
 ...

This tells me whether the current user has SELECT, INSERT, UPDATE, etc. permissions on myTable in database myDatabase.

Is it possible to easily find out why the user has these permissions? For example, I'd love to have a function fn_my_permissions_ex which outputs an additional reason column:

USE myDatabase;
SELECT * FROM fn_my_permissions_ex('dbo.myTable', 'OBJECT')

 entity_name | subentity_name | permission_name | reason
------------------------------------------------------------------------------------------------------------------------------------
 dbo.myTable |                | SELECT          | granted to database role public
 dbo.myTable |                | UPDATE          | member of group MYDOMAIN\Superusers, which belongs to database role db_datawriter
 ...

Unfortunately, I could not find such a function in the SQL Server documentation. Is there a tool or script that provides this functionality?


texts: Providing production MySQL access to outside company

texts:
We have one main production MySQL database with a read replica. This database has some sensitive data from clients that we need to protect.

My goal is to provide access to an external Business Intelligence reporting tool. As with all tools like this, they all say "we have great security, and we never store anything that we don't need, etc." I am still paranoid.

So what I am considering creating a bunch of views on the replica database (read-only) which leave out sensitive data and provide the minimum information that they need. After that, I will provision a new user on the database that only has access to those views. This way they only see the data that I want to give them and it's already read-only so nothing can be modified.

My question is, is this a good way to go about this considering my database has sensitive client information? In general, is this a relatively secure approach?


texts: Architecture alternatives to solve large table issues in Postgres 9.0

texts:
We use PostgreSQL 9.0 for our application and there is a table called events with a lot of metadata columns about event itself. The most important is resource_id that tells for which resource is related.

This table has being growing a lot and currently stores only 6 months of data. Its table_size is ~200 GB with 456 millions rows.

To optimize our most common searches we have two indexes:

timestamp
timestamp with resource_id

These indexes are used frequently and its size is ~160 GB.

This scenario that I've described is happening in a single Master instance without Slaves servers nor sharding.

Now we intend to extend this period from 6 months to 5 years of data. Estimating this amount in data would be something about ~4 billions rows with a table_size about ~ 2 TB of data.

So our indexes will have something about 1.6 TB (which I'm really worried to run in a single instance).

We had some ideas to improve this such as:

Remove indexes from table and store all that data in database focused in Data warehousing and indexing all this data in an ElasticSearch cluster
Remove index from table, indexing data in an ElasticSearch but with only ID in its body. So I would only use ElasticSearch to search and database to retrieve this data
Give a try in InfluxDB (time-series database), filtering and selecting data from all this 5 years directly from it
Upgrade PostgreSQL to 9.6, clusterize (Master + 2 Slaves) and/or partitioning

We're looking for scalable solutions that proper solves this scenario best-way possible. Thoughts?


texts:
As first sorry if I'm wrong with this here. For forwarding to the Right Place I'm grateful.

To my issue: I'm in an internship as a programmer, no real experience with databases, and as I'm open-minded accepted to compare Microsoft SQL Server with MongoDB if there would be benefits for the company to change to it.

For the original data I've selected the kinda biggest 3 tables.

Table: Article with over ~1,2 Mrd records

x-----------------------------x
| article_id|  suppID | ...   |
x-----------|---------|-------x
|    1      |     5   | ...   |
|    2      |     6   | ...   |
|    3      |     7   | ...   |
x-----------------------------x

Table: Price with over ~2,3 Mrd records

x-----------------------------x
| price_id  |ArticleID| ...   |
x-----------|---------|-------x
|    1      |     1   | ...   |
|    2      |     2   | ...   |
|    3      |     3   | ...   |
x-----------------------------x

Table: Price_Archive with over ~5,8 Mrd records

x-----------------------------x
| article_id|  country| ...   |
x-----------|---------|-------x
|    1      |     DE  | ...   |
|    2      |     EN  | ...   |
|    3      |     EN  | ...   |
x-----------------------------x

What are the main differences of SQL Server and NoSQL did I understand. But now more for the Technical Tests how can I achieve a comparison?

As for migration I do a simple Copy Paste and use the $lookup for some test scenarios. Furthermore creating a main single collection (Articles) and integrate the other 2 Tables in it, as its wanted in MongoDB-Document-Oriented.

Are there free tools where can log how much memory/time and so on is used for queries? Which other cases should I check?


texts: SQL Server Backup , Job with embeded SQL

texts:
I have a SQL Server 2008 R2 instance containing lots of databases that come and go. Each time a database leaves the server or another one pops in, I need to go through a killer routine:

I run this script in SQL Server Management Studio

SET NOCOUNT ON

SELECT 'BACKUP DATABASE [' +
name +
'] TO  DISK = N''C:\Program Files\Microsoft SQL Server\MSSQL10_50.HART\MSSQL\Backup\' + 'BCK\' +name +
'.bak'' WITH NOFORMAT, INIT,  NAME = N''Full Database Backup'', SKIP, NOREWIND, NOUNLOAD,  STATS = 10'
FROM sys.databases
WHERE state_desc = 'ONLINE'
AND name NOT IN ('master','tempdb','model','msdb','blah','blah2')

SET NOCOUNT OFF

,I get the result to text and I paste it inside the SQL Agent Job Step window.

Is there a chance that I could put this script inside the SQL Server Agent Jobs so every time a database leaves or gets into my server, I won't have to manually change my Job Step definition?

Thank you!


texts: Performing a Transaction Log Backup on a Live Database

texts:
I have a server 2012 database that has a transaction log that is growing at a rapid rate. i have a job that runs once a day after production hours which backs up the log file and shrinks it.

The number of transactions being processed has increased exponentially therefore the log is growing at such a rapid rate.

what i would like to know is .... is it safe to do a backup during production hours?


texts: Check whether a key value is referenced in any related table

texts:
I have one table in the database having ID as the primary key. Now this ID is referenced (as Foreign Key) in many other tables. How can I create a result with each row that whether it is referenced or not. As I want to prevent users from deleting items that have references. So the output should be:

ID   Title(Another Column in the table)   IsReferenced
--   ----------------------------------   ---------------------------------
1    Title 1                              True (or Count any one will work)
2    Title 2                              True
3    Title 3                              False
4    Title 4                              False


texts: Multiple counts in one table output should be one table

texts:
I want to create the following mysql query to put multiple count run on same table and output should be in one table only using as name.

select
    count(select count(TXN)
          from sale1
          where date(SALEDATE) = DATE(DATE_SUB(NOW(), INTERVAL 1 DAY))
         ) as c1,
    count(select count(TXN)
          from sale1
          where RESPONSE='Y'
            AND date(SALEDATE) = DATE(DATE_SUB(NOW(), INTERVAL 1 DAY))
         ) as c2
FROM sale1;


texts: Referencing a stored procedure by a name description across all databases in a SQL Server 2000 Instance

texts: Does anyone have a valid method in referencing system information to search for a stored procedure across all databases in a SQL Server 2000 instance? I am currently tasked with migrating crystal reports to SSRS and the customer has roughly 500 or more crystal reports with stored procedures spread over multiple databases. I know I can filter each database's programmability folder by text but I was looking for an outcome in which I can query master.dbo.sysdatabases and reference that to syscomments in which I can specify text and find a stored procedure tied to a database.