WARN max_connections from pt-variable-advisor - what to configure for DBaaS? Our VM configuration (hosted on Vmware)

# cat /proc/cpuinfo |grep "cpu cores" | awk -F: '{ num+=$2 } END{ print "cpu cores", num }'
cpu cores 32

# free -h
             total       used       free     shared    buffers     cached
Mem:           62G        23G        39G       500K       349M        10G
-/+ buffers/cache:        12G        50G
Swap:          50G         0B        50G

I got from pt-variable-advisor a warning about max_connections:

pt-variable-advisor h=localhost,u=root,p=Quule0juqu7aifohvo2Ahratit --socket /var/vcap/sys/run/mysql/mysqld.sock
(...)
# WARN max_connections: If the server ever really has more than a thousand threads running, then the system is likely to spend more time scheduling threads than really doing useful work.
(...)

Why? Any details?

Configuration in my.cnf

max_connections                 = 15360

settings from prd db cluster (MariaDB 10.1.x and Galera)

MariaDB [(none)]> SHOW STATUS WHERE `variable_name` = 'Threads_connected';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Threads_connected | 718   |
+-------------------+-------+
1 row in set (0.01 sec)

MariaDB [(none)]> SHOW STATUS WHERE `variable_name` = 'Max_used_connections';
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| Max_used_connections | 924   |
+----------------------+-------+
1 row in set (0.02 sec)

I found a MySQL manual and an (very) old similar question.

The default value is 151 to improve performance

  The number of connections permitted is controlled by the
  max_connections system variable. The default value is 151 to improve
  performance when MySQL is used with the Apache Web server.
  (Previously, the default was 100.) If you need to support more
  connections, you should set a larger value for this variable.

and

  The maximum number of connections MySQL supports depends on the
  quality of the thread library on a given platform, the amount of RAM
  available, how much RAM is used for each connection, the workload from
  each connection, and the desired response time. Linux or Solaris
  should be able to support at least 500 to 1000 simultaneous
  connections routinely and as many as 10,000 connections

We currently have 460 users and every user is allowed to do 100 max_connections. This would be the max value. Is the 100 max_connections per user and database too high? With modern connection pooling we can set this to 20? How we should configure that not risking overloading our server with context switching? Is it possible that one web app use one connection (every statement over same connection)?

There is a difference between Threads_running and Threads_connected.  The former implies that many queries are simultaneously doing something.  The other are sleeping, waiting for the user (or app) to present another SQL statement to the server.

max_connections is a limit on Threads_connected.  (Max_used_connections is a "high-water-mark".)

A typical busy system will have only 1 in 7 'connected' threads actually running.  And Threads_connected is typically much less than max_connections.  (Like 20x)

But, in bad times, too many connections each doing some slow query, leads to MySQL stumbling over itself.  Throughput (queries/second) hits some maximum and may even go down; latency goes through the roof, and a panicky DBA will reboot the machine because he thinks it has crashed, not just gotten terribly tangled up.

The solution is to find the slow queries and figure out how to speed them up.  Or re-architect the app to be more efficient.  Or change the schema and indexes.  It is rarely to get bigger hardware or change the tunables.

In a well-managed MySQL server, I rarely see more than 2-3 cores in use.  Yet, thousands of queries/second can be performed even with very few cores active.

In DBaaS, you are at the mercy of the customers.  Any one of them can write a sloppy app that hogs connections, cores, I/O, network bandwidth, anything.

Consider limiting each user to 10 connections unless he explains what he needs to do and/or pays you more money.  With the extra money, you can isolate him in a separate VM.  I once estimated, in a shop with Apache and MySQL, that 10 threads would swamp Apache, to MaxChild needed to be much lower than the default.  (We had rather heavy pages.)  If you have control over web servers, limit how many threads they have.  That is stop the problem before it gets to MySQL.

By putting every user in his own VM, you consume extra resources, but you can prevent the excesses of one from harming the others.

I'll bet many of your clients use WordPress?  Here's one performance patch to the schema:  http://mysql.rjweb.org/doc.php/index_cookbook_mysql#speeding_up_wp_postmeta


Oracle SQL Statement alter index and add partition I have an index in which I have to remove one column and reindex back for rebuild:

ALTER INDEX <index_name> REBUILD;

Is it possible to add partition when I rebuild an index.  Partition will be based on one of the column index which is a datetime field. Something like:

ALTER INDEX <index_name> REBUILD, PARTITION BY RANGE(COLLECTIONTIME) INTERVAL     (INTERVAL '15' MINUTE)
(PARTITION INITIAL_PARTITION VALUES LESS THAN (DATE '2014-10-10') );

Not sure how to write the sql statement for it. Anyone can help?

Also, if it is possible will the existing records will also be partitioned?

No its not possible to Partition a non partitioned index using rebuild in Oracle.

For partitioning an index you will need to Drop the index and then create a partitioned index.

Hope it Helps

Vishad


Changing ORDER BY direction causes performance issue I use MySQL 5.6, and I have two tables each with 16M rows:

CREATE TABLE IF NOT EXISTS `newsstudios` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=16855382 ;

CREATE TABLE IF NOT EXISTS `newsstudio_categories` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `newsstudio_id` int(11) NOT NULL,
  `category_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `newsstudio_id` (`newsstudio_id`),
  KEY `category_id` (`category_id`),
  KEY `newsstudio_id_category_id` (`newsstudio_id`,`category_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=16864013 ;

I have one query with order by order by newsstudios.id ASC :

SELECT SQL_NO_CACHE id FROM `newsstudios` WHERE exists
(
  select newsstudio_id from newsstudio_categories
  where newsstudios.id=newsstudio_categories.newsstudio_id
  and newsstudio_categories.category_id in (1303,1313,1323,1333,1343,632)
)
order by newsstudios.id limit 5;

the result of this query is:

+------+
| id   |
+------+
|   27 |
|   47 |
|   87 |
|  110 |
|  181 |
+------+
5 rows in set (0.19 sec)

but when I change the direction of order by to DESC the query execution time decreases 100 times:

+------+
| id   |
+------+
| 98232|
| 98111|
| 95222|
| 88132|
| 78181|
+------+
5 rows in set (21 sec)

First: why does this change in the direction of order by cause this huge difference in performance?

Second: before this query I have tried LEFT JOIN and WHERE IN queries instead of WHERE EXISTS but they have duplicate result that I should use GROUP BY that it cause using filesort and using temporary which decreases performance a lot. Do you have any suggestions for the query to have better performance?

If the different categories you are searching on are not to many and you can build the more complex query, this will use an index on (category_id, newsstudio_id) and I'd expect it to  be more efficient than your previous query, with either ASC or DESC choice.

It's a bit more complex to construct and I'd expect efficiency to degrade if the number of categories are tens or hundreds:

SELECT newsstudio_id
FROM
  ( ( SELECT newsstudio_id  FROM newsstudio_categories  WHERE category_id = 1303
      ORDER BY newsstudio_id DESC  LIMIT 5
    )
    UNION
    ( SELECT newsstudio_id  FROM newsstudio_categories  WHERE category_id = 1313
      ORDER BY newsstudio_id DESC  LIMIT 5
    )
      ...
    UNION
    ( SELECT newsstudio_id  FROM newsstudio_categories  WHERE category_id = 632
      ORDER BY newsstudio_id DESC  LIMIT 5
    )
  ) AS tmp
ORDER BY newsstudio_id DESC
LIMIT 5 ;


Can you switch recovery mode to simple from full in an Always ON cluster setup? I need help understanding my options.
Given:

SQL server cluster setup that is maintained by a DBA.
System is NOT in production yet (not handed over to the customer).
Initial high volume push of data that has NO breaks for days
I can stop and start the push of data and change the database at any
point. Basically I can stop all input.

I recommend to our DBA that we switch to simple mode because we are seeing within the activity monitor a pile up of queries in a wait state which eventually causes errors for the application servers.
I've never had this problem before and in a process of elimination I see we are in FULL recovery mode instead of SIMPLE.
I asked for us to switch to SIMPLE from FULL and this is the reply I get.  Is there another option perhaps that the DBA isn't aware of?
How can I help?

"We cannot switch to simple using AlwaysOn in SQL."

Can I turn off AlwaysOn for the initial loading phase or am I out of luck?
--- Addition ----

The DBA is correct - if the database is part of an Availability Group (AG), it has to be in the FULL recovery model.  This is because of the way AGs work - they ship transaction log blocks from the primary server to the secondary server(s).  So all the detailed log records provided by the FULL recovery model are needed.
You could remove the database from the AG during your initial data load.
ALTER AVAILABILITY GROUP [YourAG]
REMOVE DATABASE [YourDatabaseName];

However, the (potentially major) downside to this is that the secondary will need to be "re-initialized" once the data load is done.  That means either using "automatic seeding" to replicate the entire primary database to the secondary server(s) (Enable automatic seeding on an existing availability group), or taking a backup of the primary, restoring it on each of the secondaries, and then re-adding the database to the AG (Prepare a secondary database for an Always On availability group).
Depending on how big the database is after the data load, this might be "no big deal," or it could be a real hassle.  But yeah, that's the other option available to the DBA basically.

By the way, regarding this:

we are seeing within the activity monitor a pile up of queries in a wait state

You didn't mention the actual wait, but unless it's HADR_SYNC_COMMIT or WRITELOG, removing from the AG / switching to SIMPLE recovery might not help.
Even if these are your waits, be aware that you are treating a symptom.  If the app ever needs to dump in data like this once it's live, you probably won't be able to monkey with the AG or recovery models.
Maybe your app could reduce the amount of log data generated by grouping some of its transactions into larger batches.
Or maybe you need to investigate the network speed between the primary and secondary servers (keeping in mind how to properly measure AG throughput).
Those are just some possible remedies.  All of this is just to say that you're better off, in the long run, figuring out the root cause of this "wait state leading to application errors" business now rather than later.

The waits you've showed are lock waits, indicative of a classic blocking chain.
Notice the "Blocked By" column is 131 for all of the rows in the screenshot.  That's a "Session ID" (first column of Activity Monitor).  Keep following that chain until you find the lead blocker ("Blocked By" will be blank), and look in the "Wait Type" column to see what it is waiting on.  That's where you should focus your efforts to unravel the root cause.

Regarding this update:

Apparently the default for doing a backup of the logging directory was set to a large value. When that kicked off to clear out the logging (incremental log thing??), it makes the whole system stutter and caused the issue. That's my current theory. Now he has changed it to 5 minutes and so far, all we see is a stutter and recovery on our side. I have my engineer gather more real data (the actual block if he can catch it).

It sounds like increased resource usage when transaction log backups occur is interfering with your workload.  One solution to this would be to run transaction logs more frequently, as the DBA did.  You could even run the backups every minute if they finish fast enough.
All that said, the original solution of switching to SIMPLE recovery is a viable option if it's feasible to re-initialize the secondary after the initial load is done.


measure the traffic throughput on mysql table In order to split mysql database into two different databases on different hosts. I would like to know few things:

what is the size of the tables.
what is the traffic throughput to those tables.

Size of the tables can be determined easily, but how to find traffic throughput to tables.

How to identify how many data was retrieved from specific table?

Try MONyog which has Disk Info tab which shows tables size
also you will come to know most accessed tables using Real-Time feature of MONyog.


Cannot connect after Availability Group automatic failover We have a SQL Server setup with Availability Groups. In the cluster we have four nodes, two in one datacenter and two in another datacenter.
There are four AGs on these cluster nodes, each primarily on one of the four nodes. One other node is in the same datacenter for synchronous HA and the other two for asynchronous in the other datacenter for DR.
We have one listener for each AG.
One AG seems to automatically failover at certain times due to various Network issues; however, the AG seems to failover fine. The HA secondary becomes primary, and all databases come online. However, applications seem unable to connect and we see various connection error type messages such as:

Cannot connect to SQL Server instance 'HH-SQL-D11' :
A transport-level error has occurred when receiving results from the
server. (provider: TCP Provider, error: 0 -
The semaphore timeout period has expired.) : The semaphore timeout
period has expired [121] (requires acknowledgement)

and

named pipes provider error 40 the network path was not found

Yet when we failover manually the connection is fine and works as expected. Other AGs on the cluster have automatically failed over and have worked fine. I have checked various things like the SQL Instance is up, TCP/IP and Named pipes enabled, remote connections enabled. I have asked the networks team to check things like firewalls and they say they are all ok.
I have tried to read the cluster logs but I am not sure what to look for in here and I am unsure of how to fix this issue. It's a problem as it means that HA is not operating. And if it does failover automatically, applications connecting to it will not work.

If you cannot reach the listener from SSMS, then it seems to be an issue with networking or name resolution (DNS).
You can try do to a NSLOOKUP of your listener and make sure it point at the primary node. If not, it means you have an issue with DNS update. Your windows admin should be able to help you.
If it points to the right server, but you cannot ping it or connect to it, then I would provide that information to the network team, asking them to assist with this specific situation.


SQL Server - Alter table vs drop and create Our database is SQL Server 2008 R2. We have some tables that have some varchar(500) columns that I want to switch to datetime2 or bigint. I can guarantee all the data in the columns to be switched are valid for the proper type. The column changes do affect indexes, but not keys.

While discussing with colleagues, we have come to two ways to approach the problem. Both these would be done through T-Sql scripts.

Create a temp table via select into, drop the old table and recreate the table with the proper datatypes. Recreate the indexes.
Alter the current table/data types via
ALTER TABLE x ALTER COLUMN Y datetime2 and then rebuild or recreate the indexes.

Because I am confident the data will convert cleanly, I am leaning towards #2. My colleague and a DBA friend prefer #1 but my colleague can't remember why they trained him that way. The DBA friend is on vacation so I didn't ask him why.

Can someone provide insight on which option they think is better and why? Ultimately it is my decision and I am wondering why #1 would be preferred over #2?

I recently did this in my organization wherein we wanted to handle a table with billion + rows.

All the credit for the idea goes to Aaron Bertrand and is from his blog post Trick Shots : Schema Switch-A-Roo

Test below process on a small table and get your self comfortable before doing it in PROD.

create 2 schemas fake and shadow with authorization dbo.
Create a table with the columns and data types you want in shadow schema e.g. create table shadow.Correct_Table ...
Insert the data and create all the indexes that the original table has in the shadow schema table.
This way you have identical copies of table with data and indexes but they are in different schemas (logically separated).
Once done update stats on the table with shadow schema.
Switch the schemas (This is a metadata operation and is extremely fast)

--- ALTER SCHEMA TargetSchema TRANSFER SourceSchema.TableName;

BEGIN TRANSACTION;

  ALTER SCHEMA fake TRANSFER     dbo.original_table;
  ALTER SCHEMA dbo  TRANSFER  shadow.Correct_Table;

COMMIT TRANSACTION;

ALTER SCHEMA shadow TRANSFER fake.Lookup;

Do a final check to see if everything went as planned. You should do a select count(1) from dbo.Correct_table
Once step 7 is confirmed and you are happy, drop the shadow.table, shadow schema and fake schema as clean up.


Choice of connection pooling library for VM deploys HickariCP docs say:

  HikariCP relies heavily on accurate high-resolution timers for both
  performance and reliability. It is imperative that your server is
  synchronized with a time-source such as an NTP server. Especially if
  your server is running within a virtual machine. Do not rely on
  hypervisor settings to "synchronize" the clock of the virtual machine.
  Configure time-source synchronization inside the virtual machine. If
  you come asking for support on an issue that turns out to be caused by
  lack time synchronization, you will be taunted publicly on Twitter.

Which other connection pooling libraries would you choose, which are less sensitive to virtual machine time drifts?

Any pool in an environment in which time can flow backwards or leap forwards, which it can in a virtual machine, is going to be subject to spurious failure when it does so.

Moreover, it is not just connection pools that are affected.

Any...

Timed waits
Timed polls from a concurrent collection
Object.wait() with a timeout
Thread.sleep() call
...
Anything in Java that requires the measurment of time is affected.

If you cannot nail down your clock drift, you will encounter unexplained application failures, occurring in your code and third-party libraries.

HikariCP (i.e.me) got sick of spending hours trouble-shooting a user's issue, reading logs and thread dumps, only to discover their clock had jumped backwards or forwards more than a minute.

User: "Why did I get a bunch of connection timeouts?!"

(several days of back-and-forth questions and examining logs later...)

HikariCP: "Well, you asked the pool to try for 30 seconds to give you a connection ... then your clock jumped forward 42 seconds and Java (and the OS) unparked all the threads as if time had really passed."

It is really simple to fix, just configure NTP inside your VM.


Oracle : How to grant to a ROLE the ability to grant a ROLE to a USER? I have a role : ADMIN_ROLE

I'm connected as ADMIN_ROLE and created a TEST_USER and TEST_ROLE

I would like to be able to grant TEST_ROLE to TEST_USER using ADMIN_ROLE

I did (using system user) :

Grant create role to ADMIN_ROLE with admin option;
Grant create user to ADMIN_ROLE with admin option;

but when executing from ADMIN_ROLE :

Grant TEST_ROLE to TEST_USER

I'm receiving error:

  ORA 1932 - ADMIN option not granted for role string.

Any ideas please ?

works fine for me on 11.2:

CONN SYSTEM/<>
CREATE ROLE admin_role;
Grant create role to ADMIN_ROLE with admin option;
Grant create user to ADMIN_ROLE with admin option;
CREATE USER john IDENTIFIED BY john;
Grant admin_ROLE to john;
GRANT CONNECT, RESOURCE TO john;
CREATE USER john2 IDENTIFIED BY john2;
GRANT CONNECT, RESOURCE TO john2;

CONN john/john
CREATE ROLE test_role;
grant test_role TO john2;

DROP ROLE ADMIN_ROLE;
DROP ROLE TEST_ROLE;
DROP USER john CASCADE;
DROP USER john2 CASCADE;

Grant, executed in 10 ms

Try redoing it, you probably missed an admin option when you tried for the first time.
Drop everything.


How to get Kerberos working against a named SQL Server instance in Power BI Report Server? I have a Power BI Report Server working perfectly connecting to SQL Server machines with a default instance only. I can also connect to SSAS named instances with the proper SPNs and by allowing Kerberos constrained delegation from the Power BI service account to the MSOLAPSVC.3 service (SQL Server Browser). I believe I have all proper SPNs set up properly as I can run linked servers and query just about anything from anything. Kerberos Configuration Manager shows green check marks for everything.

My only problem is connecting to named instances of the SQL Server engine (not SSAS). I get the error below, which you would normally get if SPNs are not registered properly.

  DESCRIPTION:  Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.
  Reason: Could not find a login matching the name provided. [CLIENT:
  x.x.x.x]

Is there a difference in how named instances must be connected to from Power BI (and perhaps SSRS as well)? I am trying to do a DirectQuery against a remote named instance of SQL Server in a .pbix file in my environment.

After hours of troubleshooting, I've got this working. I had to allow the PBI service account to pass credentials to the destination SQL Server(s) on an EXPLICIT port, even when connecting to a default instance on 1433. If anyone has any input, feel free to comment, but here's what worked for me:

Open Active Directory Users and Computers
Right-click the service account running Power BI Report Service, and click "Properties"
Click the "Delegation" tab, then "Add", then "Users or Computers"
Enter the service account of the DESTINATION SQL Server instance and click "OK"
Click the MSSQLSVC Service that shows a port, whether it's the default 1433 or another port. If you have multiple named instances, add the entry for each port number.
Click OK, then OK again.
Restart the Power BI Report Server MACHINE, not just the service.
Open a Power BI (.pbix) file that has uses DirectQuery against a different SQL Server instance.
Credentials should now be passing from you (the client), to Power BI Report Server, and then on to any destination server(s).


MySQL Connection Error for TDV I am trying to connect to a MySQL database using Tibco Data Virtualization Studio (TDV).

I was able to connect to the database using MySQLWorkbench. I checked my.cnf and skip-networking is not present.

TDV is throwing the following error:

    "xx.xx.xx.xx:3306@" with the supplied connection information.  [datasrc-3961050]
Unable to connect to data source "/shared/Admin Database/Data Sources/CACHE DATABASE/MySQL Cache" at
    "jdbc:mysql://xx.xx.xx.xx:3306/DBNAME".  Make sure the URL information, user name and password
    are correct.  [datasrc-3961025]
Cause: Communications link failure

The last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any packets from the server. [Log ID: 85a492e6-04c4-491a-be42-d9b29eb93472

Any ideas how I can correct this error?

We connected with Tibco support to resolve this issue.

Steps:

Change jar from mysql-connector-java-8.0.11.jar to mysql-connector-java-5.1.48.jar. Place the jar file "mysql-connector-java-5.1.48.jar" in location
"\conf\adapters\system\mysql_5_5". Then restart the TDV server.
Create a new MySql 5.5 datasource with same credentials. Open the advanced settings panel for the new datasource. Then change the connection format to "mysql://xx.xx.xx.xx:3306/DBName?useSSL=false&allowPublicKeyRetrieval=true"

Connection is successful.


Don't know how to make two selects bridge together ad run a single query I got the races to work then I added another query in addition to the same statement but when I ran the query and it only show student number, dob, gender and ethnicity but did not show another select with races add to.  I cannot figure it out.

select s.student_number,s.dob,s.gender,s.ethnicity
from students s,
(select lastfirst,grade_level,
   max(case when rn = 1 then racecd end) race1, -- find 1st row
   max(case when rn = 2 then racecd end) race2, -- find 2nd row
   max(case when rn = 3 then racecd end) race3  -- find 3rd row
from
 (
   select
      s.lastfirst,s.grade_level,sr.racecd,
      row_number() -- one row per race, numbered 1,2,3,...
      over (partition by sr.studentid
            order by sr.racecd) rn
   from students s
   join studentrace sr
     on s.id=sr.studentid
   where s.enroll_status=0
 )
 group by lastfirst,grade_level
 order by grade_level asc)

you need to reference the race fields in the initial SELECT statement (before the FROM clause) with an alias to the inner select.  I'm not quite sure what it is that you're trying to accomplish, but this is certainly more on track with what you desire.  Try this for starters

SELECT
    s.student_number,
    s.dob,
    s.gender,
    s.ethnicity,
    RaceTable.race1,
    RaceTable.race2,
    RaceTable.race3
FROM students s INNER JOIN
    (SELECT
        lastfirst, grade_level,
        max(case when rn = 1 then racecd end) race1, -- find 1st row
        max(case when rn = 2 then racecd end) race2, -- find 2nd row
        max(case when rn = 3 then racecd end) race3  -- find 3rd row
     FROM
         (SELECT
             st.lastfirst,st.grade_level,sr.racecd,
             st.student_number,st.dob,st.gender,st.ethnicity,
             row_number() -- one row per race, numbered 1,2,3,...
                 over (partition by sr.studentid
                 order by sr.racecd) rn
          FROM students st
          JOIN studentrace sr
              ON st.id=sr.studentid
          WHERE st.enroll_status=0
         ) AS RTinner
     GROUP BY lastfirst,grade_level
  -- ORDER BY grade_level asc
    ) AS RaceTable
ON s.lastfirst=Racetable.lastfirst AND
   s.grade_level=Racetable.grade_level


Relational Design - Multiple tables into one foreign key column? Take a table that tracks meat purchases.  It has a "meat_id" foreign key column to indicate what type of meat the purchase was.

But, the different types of meat are unique in some way (such as USDA grading), so I'm thinking they should be stored in different tables.

I currently don't have enough rep to post the ERD I drew out, but I hope these DDLs will be enough (I've simplified them for brevity):

CREATE TABLE meat_purchase
(
    id                  INTEGER
  , purchase_details    VARCHAR(4000) -- actually multiple columns, but details are irrelevant
  , meat_id             INTEGER
);

CREATE TABLE beef_meats
(
    id                  INTEGER
  , usda_beef_grade_id  INTEGER
        FOREIGN KEY REFERENCES usda_beef_grades
  , desc    VARCHAR(4000)
);

CREATE TABLE pork_meats
(
    id      INTEGER
  , desc    VARCHAR(4000)
);

CREATE TABLE poultry_meats
(
    id      INTEGER
  , bird_id
        FOREIGN KEY REFERENCES birds
  , desc    VARCHAR(4000)
);

-- and so on for the different types of meat...

I'm wondering how to relate the "meat_purchases" table to the meats tables.

Standard SQL and RDBMS agnostic answers only, please.

I think you are looking for a subtype/supertype construct.  Meat would be your migrating key and would contain a Type field that indicates which sub-type of meat it relates to.  So:

PurchaseMeat = Meat = {MeatBeef, MeatPork, MeatPoultry}

Where Meat is the type and the key of the subtype.

In Crow's feet notation this is a circle with a line under it.


Rebuild Index not freeing up space I had a situation yesterday where my indexes were rebuilt and database size doubled in size (50 % of the new size was unused). Sort in tempdb is set to off and I get the impression that this rebuild caused it (index rebuild not in tempdb).

Is there a reason for the rebuild procedure to hold onto disk space after completion/potential failure?

If you are expecting a rebuild to make the data files in the file system smaller, this is not how it works.

In fact, rebuilding can even lead to larger data files on disk, as new pages and extents are allocated to hold the copy of the data. When the old pages are dropped from the index, they're not actually removed from anything, they're just deallocated.

You've rebuilt your indexes and saved some space inside the data file. This means that as you add more data (to this and other tables), the data file doesn't immediately have to grow to make room for the new data.

This is a good thing. Growing a file is an expensive, blocking operation, and you want to minimize this. Shrinking the file to get back some disk space temporarily seems like a lost cause. The data file is just going to grow again later, in which case, what are you going to do with that disk space you got back for a little while? Lease it out to someone and then evict them when your database has to grow again? Just leave it at the size it has grown to now, because unless you mark it as read-only, it will more than likely grow to that size again.

The data file is just a container. Left alone, it will only ever grow. It won't shrink just because you've deleted some rows or rebuilt some indexes or dropped a table. SQL Server doesn't release that disk space and shrink the file for you for the same time it won't release memory back to the operating system just because you've dropped an index that happened to be loaded into the buffer pool: it assumes you're going to use that space / memory again, and knows that it's expensive to keep releasing it and re-claiming it. So it just holds onto it.

And, in almost all cases, you're going to want it that way. If you really need to reclaim some space that you know you won't use again, see this post.


Number of threads in Oracle XE 18c I just installed Oracle XE 18c ... In this page, they list the available features/resources. One of them is the use of two CPU threads ...

But, in practical terms, what does this mean?  Does this mean that only two queries can be executed concurrrently? or what?

From the Installation Guide ( here )

  Oracle Database XE limits itself automatically to two cores for processing. For example, on a computer with 2 dual-core CPUs (four cores), if a large number of database clients try to simultaneously execute CPU-intensive queries, then Oracle Database XE will process the queries at the rate of just two cores even if more CPU capacity is available.

I would image "large number" means ">2".


Trying to filter msdb..sysjobs by job_id I'm attempting to determine the name of the SQL Server Agent Job that is currently running by comparing the output of master.sys.dm_exec_sessions.program_name to msdb..sysjobs.job_id

I'm using the following T-SQL to try to diagnose why I cannot get the results I want:

SELECT program_name, '|' + SUBSTRING(program_name,30,34) + '|' AS JobIDSubstring, t.text
FROM sys.dm_exec_sessions s
    INNER JOIN sys.dm_exec_requests r on s.session_id = r.session_id
    CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
--  INNER JOIN msdb..sysjobsteps sj on SUBSTRING(program_name,30,34) = cast(cast(sj.job_id as varbinary(max)) as varchar(max))
WHERE
    program_name like 'SQLAgent%' ;

SELECT CAST(job_id as varbinary(max)) as JobStepID, CAST(step_id as varbinary(max)) as StepID, *
FROM msdb..sysjobsteps;

SELECT program_name, '|' + SUBSTRING(program_name,30,34) + '|' AS JobIDSubstring, t.text
FROM sys.dm_exec_sessions s
    INNER JOIN sys.dm_exec_requests r on s.session_id = r.session_id
    CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
    INNER JOIN msdb..sysjobsteps sj on SUBSTRING(program_name,30,34) = cast(cast(sj.job_id as varbinary(max)) as varchar(max))
WHERE
    program_name like 'SQLAgent%' ;

Results:

The last query should return results, however it returns zero rows since the JOIN clause is clearly not working.  How should I construct this so the JOIN from sys.dm_exec_sessions to msdb..sysjobsteps works?

Stop using CAST and use CONVERT. Specifically because, in this case, you need to use a certain style parameter in order to get the right string comparison (otherwise it is just converting 0xwhatever to the string value represented by 0xwhatever, which is not 0xwhatever. Compare:

SELECT
  CONVERT(VARCHAR(32), 0x48656C6C6F21),
  CONVERT(VARCHAR(32), 0x48656C6C6F21, 1);
  ----- only difference -------------^^^

Results:

------   --------------
Hello!   0x48656C6C6F21

Hello! is actually the string represented by the binary value. The second column is just the binary value displayed as a string. Big difference!

With that in mind, change the join criteria on your last query to:

INNER JOIN msdb.dbo.sysjobsteps sj -- why leave out dbo?
  ON SUBSTRING(s.[program_name],30,34) = -- prefix your columns
  CONVERT(VARCHAR(34), CONVERT(VARBINARY(32), sj.job_id), 1) -- no need for MAX types here
  ------- this is the important piece ------------------^^^


Options for Data Encryption in SQL Server 2008 R2 Standard Edition? I'm helping a friend with setting up encryption of data on SQL Server 2008 R2 Standard edition. Upon original research I thought I could use
TDE but did not realize that it was only available for Enterprise or DataCenter versions of SQL Servers. Upon further research into SQL Server 2008 R2 features I saw that it does allow for "Data encryption and key management" but I'm not sure what it means or how to implement it.

What is the most efficient and low cost method for data encryption? Should I do something through SQL Server or just use third party tools to encrypt the whole volume where DB and backups are? Also if someone can point to a way to use "Data encryption" that comes as feature in standard edition I would really appreciate. Every time I search for encryption on sql server I keep ending up on how to use TDE and in current scenario it is not feasible for the size of business to purchase Enterprise Edition.

Reason for encryption - HIPAA Compliance. If access is gain to file system for database or backups, data is encrypted and can not be of any use. This could include physical access to the machine or access through local admin.

Since you are using Standard Edition, you cant use TDE. So other options are

Using encryption keys at instance/database level :

SQL Server has two kinds of keys: symmetric and asymmetric. Symmetric keys use the same password to encrypt and decrypt data. Asymmetric keys use one password to encrypt data (called the public key) and another to decrypt data (called the private key).

SQL Server has two primary applications for keys: a service master key (SMK) generated on and for a SQL Server instance, and a database master key (DMK) used for a database.

Also, you can have encryption at column level by creating a MASTER KEY ENCRYPTION along with CREATE CERTIFICATE and then CREATE SYMMETRIC KEY.

An example of how this can be done is described at Encrypt a Column of Data

Reference : SQL Server and Database Encryption Keys (Database Engine)

At Drive level :

Using BitLocker as it is a Drive Encryption data protection feature available Windows Server 2008 R2. Refer to : BitLocker Drive Encryption Overview There are many opensource or third party software to do the same job but at additional cost.

Note: The most important bit is ALWAYS backup your encryption keys.

You can use third party software like Redgate's sql backup which allows you to encrypt backups using passwords.

Depending on what level you need encryption will determine if it is worth upgrading to enterprise edition or not. You have to evaluate native TDE encryption vs encryption keys and certificates vs open source vs disk encryption.


What is the fastest way to make a copy of a 5TB SQL Server DB on the same server without having to fully reinitialize the DB to add it to the AG I have a 3 node Always On AG that hosts a 5TB database. Due to business requirements, at 12:01am on the first day of every month, I need to create a copy of that database on the 3rd node (replica) appended with the previous month name. For example, on April 1st, I create a DB called "mydatabase_march2021" which is a copy of "mydatabase" current as of April 1st at 12:01am.
My current process involves removing the DB from the AG on the 3rd node promptly at 12:01am, renaming it, and then starting the slow process of backing up and restoring the 5TB DB to the 3rd node and adding it to the AG. Is there some other way I can do this without having to fully reinitialize the DB on the 3rd node? I need to have this copy available to business users by 5am.
I have tried removing the DB from the AG, offlining SQL, making a copy of the DB files, but there is no way to attach those files and keep the DB in recovering (which is necessary to reinitialize).
Additional Information:
Windows Server 2016 Standard
SQL Server 2012 SP4 Enterprise (will be upgrading to SQL 2016 or 2019 at some point in the next year)
Node 1 is in datacenter A
Node 2 and Node 3 are in datacenter B

"I could use a tranlog backup, and then roll it to 12:01am"
Do that.  You can restore the last full backup, then apply log backups with NORECOVERY in the days or hours before the first of the month, then apply the last log backup with STOPAT and RECOVERY or STANDBY.
Or you could create a Database Snapshot at midnight on the first of the month.  That would avoid copying all 5TB at the beginning, and the snapshot will only store pages that change over the course of the month.


Is worth change databases collation from Latin1_General to Modern spanish, migrating to a new server We are migrating 3 databases tables from Sql Server 2000 to a new sqlserver 2014.

I know that for new databases the recommended Collation for spanish is Modern_Spanish. But what is the recommendation for migrating database to a new server? Create the new server with the same collation of the 3 Databases (Latin1_General)? Or migrate the current dabases to Modern_Spanish.

The current sql server 2000 where the 3 databases are running the default collati is Latin1_General as the 3 database.

Is it worth to create the new server with Modern Spanish collation, and migrate databases collation to Modern Spanish? Or it's better to continue with Latin1_General ?

Thanks

Modern_Spanish and Latin1_General both support the same character set. The difference between the two is the treatment of certain characters when sorting/doing comparisons. For example in Modern_Spanish n and ñ are considered different characters (rather than a character and an accented version of the same character).

Personally I'd stick with Latin1_General as changing the collation may subtly change the behavior of applications (e.g. yesterday this list was sorted one way, today it's sorted a different way, etc.). I'd only change the collation as part of the migration if you understand and want the behavior change.


Should I extract / normalize the mailing address despite requirements limiting number / type of addresses? I currently am working on a contacts application (similar to Outlook contacts combined with eVites and LinkedIn).  The requirements for the application are to limit the addresses (and phone / email addresses) to two - primary and secondary.

Normally, I would extract the addresses into their own table with a key back to the main profile along with the address type in the address table.  However, with the specific limitation of the number of addresses, I am considering having the addresses in their own table - with a foreign key from the profile table to the address table (one foreign key for primary and one for secondary).

Essentially, my question is:

Should I prepare for possible changes in the future to allow for the capability of more than two addresses in the future - or work within the limitations of the current requirements to specifically limit (via the database schema) which types and how many addresses can be associated?

Thank you for your help.

My two proposed schemas are as follows (please forgive the formatting - having trouble with the limitations of the WYSIWG editor):

First Schema:

NOTE: code / constraints / triggers would limit it to the primary / secondary rather than the database schema

Profile

(profile information fields)

Address

AddressId
ProfileId
AddressTypeId
Street Address
City
State
Zip

AddressType

AddressTypeId
AddressType

Second Schema

Limitations are through the profile schema

Profile

...
PrimaryAddressId
SecondaryAddressId

Address

(may be further normalized)

AddressId
Street Address
City
State
Zip

Always bet on requirements changing, or being reinterpreted, at some point in the future.

Does the requirements documentation explicitly state that there are exactly two or just that there should be up to one primary and up to one secondary? If the latter then what could be meant is there could be many addresses of which up to one is the primary and up to one is the secondary and you should allow for more addresses.

Even if not, I would still err on the side of addresses being separate entities if you need to support more than one for any other given entity. The extra work now should be minimal, if you need to emulate the non-normal-form for the application logic you can by various means, and when the requirements change later your job will be a lot easier (and you can either impress the client/devs with your foresight or keep shtum and charge them as if the change request does require significant structural alterations in the DB).


Setting up the Listeners for Oracle Dataguard I setup up a Oracle 12c Dataguard configuration. To build the standby database I used the RMAN DUPLICATE command, which requires a static listener configuration.

tnsname.ora on HOSTNAMEA and HOSTNAMEB are the same:

BR =   (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = HOSTNAMEA)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SID = BR)
    )   )

BR_STBY =   (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = HOSTNAMEB)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SID = BR)
    )   )

listener.ora HOSTNAMEA:

    SID_LIST_LISTENER =
        (SID_DESC =
          (GLOBAL_DBNAME = BR_DGMGRL)
          (ORACLE_HOME = /opt/oracle/product/12200/dbhome_1)
          (SID_NAME = BR)
        )

    LISTENER =
      (DESCRIPTION_LIST =
        (DESCRIPTION =
          (ADDRESS = (PROTOCOL = TCP)(HOST = HOSTNAMEA)(PORT = 1521))
          (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
        )
  )

listener.ora HOSTNAMEB:

SID_LIST_LISTENER =
    (SID_DESC =
      (GLOBAL_DBNAME = BR_STDBY_DGMGRL)
      (ORACLE_HOME = /opt/oracle/product/12200/dbhome_1)
      (SID_NAME = BR)
    )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = HOSTNAMEB)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

This configuration is using a static listener configuration. How does the application know the database has for example switched from HOSTNAMEA to HOSTNAMEB? Should I use dynamic listener registration? how should the configuration look like?

You should have a TNS entry, in the client machine, similar to following to connect to the data guard environment.

BR =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = HOSTA)(PORT = 1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = HOSTB)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = BR)
    )
  )

BR_STBY =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = HOSTA)(PORT = 1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = HOSTB)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = BR_STBY)
    )
  )

You also should have database service configured according to their role-primary or standby as shown below(if you are using Grid Infrastructure).

srvctl add service -d BR  -s BR -l PRIMARY -m BASIC -e SELECT -w 1 -z 180
srvctl add service -d BR  -s BR_STBY -l PHYSICAL_STANDBY –m BASIC –e SELECT –w 1 –z 180
srvctl start service -s BR -s BR

If you are not using Grid Infrastructure then(AS SYSDBA on Primary)-

SQL>exec dbms_service.create_service(service_name => 'BR', network_name => 'BR', failover_method => 'BASIC', failover_type => 'SELECT', failover_retries => 10, failover_delay => 1);
SQL> exec dbms_service.create_service(service_name => 'BR_STBY', network_name => 'BR_STBY', failover_method => 'BASIC', failover_type => 'SELECT', failover_retries => 10, failover_delay => 1);
SQL> create or replace trigger MANAGE_SERVICES
  after startup on database
declare
  db_role varchar2(16);
  db_mode varchar2(20);
begin
  select database_role, open_mode into db_role, db_mode from v$database;
  if db_role = 'PRIMARY' then
    dbms_service.start_service('BR');
    dbms_service.stop_service('BR_STBY');
  elsif (db_role = 'PHYSICAL STANDBY') and (db_mode = 'READ ONLY') then
    dbms_service.start_service('BR_STBY');
    dbms_service.stop_service('BR');
  else
    dbms_service.stop_service('BR');
    dbms_service.stop_service('BR_STBY');
  end if;
end;
/
SQL>

Modify the attributes used in the above examples according to your need and refer to the Oracle documentation for details.


Compress SQL Server database Is there a way to create a SQL Server 2014 database so that all data tables will be compressed automatically?
Is there a way to compress all the existing tables of a SQL Server 2014 database?

Background: we run a data-gathering program that writes several multi-million row tables into a database which is then copied to a separate computer for offline analysis and reporting, often in a different physical location. The separate computer is not as powerful as the data-gathering computer, and has limited disk space, so the database size is an issue.

Edit: We use SQL Server 2014 Enterprise. Each table has a primary key and two additional indexes.

I'm not aware of a way to automatically compress new tables. The script below will compress existing tables and indexes that are not already compressed. You could schedule this periodically to compress new tables if you don't have control over the table and index create DDL.

DECLARE @SQL nvarchar(MAX) = (
    SELECT
          N'ALTER '
          + CASE
                WHEN type_desc ='HEAP'
                THEN 'TABLE '
                + QUOTENAME(OBJECT_SCHEMA_NAME(object_id))
                + N'.'
                + QUOTENAME(OBJECT_NAME(object_id))
                ELSE 'INDEX '
                + QUOTENAME(name)
                + N' ON '
                + QUOTENAME(OBJECT_SCHEMA_NAME(object_id))
                + N'.'
                + QUOTENAME(OBJECT_NAME(object_id))
        END
        + N' REBUILD WITH(DATA_COMPRESSION=ROW);
'
    FROM sys.indexes
    WHERE
        OBJECTPROPERTYEX(object_id, 'IsMSShipped') = 0
        AND EXISTS(
            SELECT 1
            FROM sys.partitions
            WHERE
                partitions.object_id = indexes.object_id
                AND partitions.index_id = indexes.index_id
                AND data_compression_desc = N'NONE'
            )
    FOR XML PATH(''), TYPE).value('.', 'nvarchar(MAX)');
EXEC sp_executesql @SQL;

Note that the compression feature is only available in Enterprise and Developer Editions in SQL Server 2014 and earlier versions. It is also available in Standard Edition beginning with SQL Server 2016 SP1.


Adding column into MySQL table size 150GB I am trying to add column into MySQL table
Table size 150GB
Table storage engine= innodb
MySQL version=5.6.34
Table have already created primary key(cluster index).

More than 2 hours completed and tmp directory taken 207 GB space for this operation and still not completed
I tried algorithm =inplace and lock=none then I got syntax error and I was trying to type correct syntax but no luck so I decided to do only write add column query .

My question is why MySQL uses more than 150GB tmp size and still consuming size and not completed yet. I have no idea when it will complete . I checked .sql file size which is created while add column query running The size 1.1TB how?

Table have 4 indexes and aut_increment already on table
Please help me...

For 5.6:  pt-online-schema-change is probably your best bet.  It creates a new table with the change, then gradually copies rows over.  Meanwhile, it uses a TRIGGER to keep the new copy updated.

For 8.0:  ADD COLUMN now has ALGORITHM=INSTANT.

Would you like to show us SHOW CREATE TABLE; there may be some improvements that would decrease the problem.


Should I create separate table or have the information as columns in the same table Sorry for the title but I could not find anything better that could suit my needs. I'm designing the database of a Document Management (Circulation) application. There are two types of documents - A and B. I've created a table one for each. Any document independent from its type has Instructions information that has to be attached to it. Here are the details of the Instrcutions:

Executors (1 to many on another table)
Deadline
Target

I know that there's a one to one relationship between the document types and Instructions, so according to database normalization I should not have a separate table for it. But on the other hand I'll have to include the same columns in two different tables. What do you think?

P.S. I won't be able to create a foreign key constraint between Instructions table and the two other.

If you really want to normalize your design, here is a suggestion to do so. You should be able to create foreign key constraints. This is without the extra Instruction table since all documents will have the instruction details, stored in the Document table:

Executor
----------
ExecutorID

Document
----------
DocumentID
ExecutorID
Deadline
Target
-- other columns, relative to all documents

DocumentA
----------
DocumentID
-- other columns, relative only to documents of type A

DocumentB
----------
DocumentID
-- other columns, relative only to documents of type B

and their relationships:

              Executor
                /1
               /
              /
             /0..N
         Document
         1/    \1
         /      \
        /        \
   0..1/          \0..1
 DocumentA      DocumentB

*Note: the only constraint not shown (and enforced) with the above design is that a document has to be of either A or B type (and not both.) This can be enforced as well, with a somewhat more complex design.


Optimising a query with two aggregate fields I have a table in a MySQL 5.5 database with around 7 million records, with fields typeId, regionId, scanTime, scanId and a query that returns around 239,000 results.

For every combination of typeId and regionId, I want to find the most recent scanId/scanTime. (There are a small number of regionIds and a large number of typeIds).

I initially approached this with the following naive query:

SELECT max(scanTime), max(scanId), typeId, regionId
FROM scanTimes
GROUP BY typeId, regionId;

At this time there was the primary key index on scanId and an index on typeId, regionId, and the query took a few minutes as it had to read the entire database to calculate the aggregates.

As the scanId is auto-incrementing, and time shouldn't run backwards under normal conditions, carrying out an aggregate on both scanId and scanTime seemed a bit wasteful - it should surely be possible to just select the record containing the highest Id, then retrieve the matching Time.

So I adjusted my query to use a subquery as follows:

SELECT sq.maxScanId, q.scanTime, q.regionId, q.typeId
FROM
  (SELECT max(scanId) as maxScanId, regionId, typeId
  FROM scanTimes
  GROUP BY regionId, typeId
  ) sq
INNER JOIN scanTimes as q
ON q.scanId = sq.maxScanId AND q.regionId = sq.regionId AND q.typeId=sq.typeId

I also added an index on typeId, regionId, scanId, and another on typeId, regionId, scanTime (though it turns out I don't need the latter).

This got the query down to a few seconds which is a big improvement. Is it possible to do any better than this?

This is the result of EXPLAIN:

+----+-------------+--------------------+--------+-------------------------------------------------------------+--------------------+---------+--------------+--------+--------------------------+
| id | select_type | table              | type   | possible_keys                                               | key                | key_len | ref          | rows   | Extra                    |
+----+-------------+--------------------+--------+-------------------------------------------------------------+--------------------+---------+--------------+--------+--------------------------+
|  1 | PRIMARY     | <derived2>         | ALL    | NULL                                                        | NULL               | NULL    | NULL         | 239307 |                          |
|  1 | PRIMARY     | q                  | eq_ref | PRIMARY,REGION_TYPE,SCANTIME_REGION_TYPE,region_type_scanid | PRIMARY            | 4       | sq.maxScanId |      1 | Using where              |
|  2 | DERIVED     | scanTimes          | range  | NULL                                                        | region_type_scanid | 8       | NULL         |    201 | Using index for group-by |
+----+-------------+--------------------+--------+-------------------------------------------------------------+--------------------+---------+--------------+--------+--------------------------+

Is it possible to optimise this query further?

What database?  Does your database support row_number().

And you changed the query.  In the first max(scanTime), max(scanId) are not necessarily on the same row.  Those are two independent max.

The scanTime of the row with the max(scanID) is different and what you got with the second.  It is faster as scanID is indexed (PK) and scanTime is not. Since scanID is the PK you don't need:

AND q.regionId = sq.regionId AND q.typeId=sq.typeId

If your database supports row_number()

select * from
(
SELECT scanTime, mscanId, typeId, regionId
     , row_number over (partition by typeId, regionId order by mscanId desc) rn
FROM scanTimes
) tt
where tt.rn = 1


SSIS Buffer/Memory Failure I get this error when running SSIS package...

A buffer failed while allocating 104857600 bytes.
The system reports 31 percent memory load. There are 17179328512 bytes of physical memory with 11714314240 bytes free. There are 2147352576 bytes of virtual memory with 406519808 bytes free. The paging file has 19729465344 bytes with 13419847680 bytes free.

The memory seems ok on the machine that the package is running on. Is 31% memory load considered high?
This has only started to happen after migrating to a newer server. But this new server has double the memory capacity.

No new SSIS packages have been added.
The SSIS packages are stored on SQL Server 2008R2 server. They are called from another server that has SSIS 2008 running.

Any idea what may be causing this?

The SSIS package is executed through a 3rd party application. We forced this application to run as 32 bit as we were getting the below error after adding a reference to System.Data.dll

How do I force the SSIS package to run as 64bit?

2,147,352,576 bytes is 2Gb which implies you have a 32-bit SSIS running on 32-bit Windows, so the 16Gb total memory of which 10.9 is free is fairly meaningless but is where the 31% comes from: (17,179,328,512 - 11,714,314,240) / 17,179,328,512 = 0.318.

A 32-bit process can only use 2GB (or under some configurations 3Gb) unless it is compiled LARGEADDRESSAWARE and running on a 64-bit edition of Windows. On 64-bit Windows a large-address aware process should be able to use 4Gb (see this which refers to here).

In this case the process seems to have ~386Mb available in the 2GB address space and is failing to allocate 100Mb which is less than that - one educated guess is that this could be due to fragmentation: if SSIS is wanting a single coherent block and the remaining free address space is in blocks smaller than that spread around the address space.

No new SSIS packages have been added.

The change is likely due to increases in incoming data size in that case.

Possible Solutions

If you are running a 32-bit Windows edition you could try using the /3GB switch to ask Windows to use the 3+1 address space arrangement for 32 bit processes instead of 2+2 (see https://technet.microsoft.com/en-us/library/bb124810(v=exchg.65).aspx or similar reference for your edition of Windows) - if SSIS is capable of using the larger address space this will give it a bit more room.

If you are using a 64-bit Windows install and 64-bit SQL then try to make SSIS use its 64-bit run-time. There are issues with some data access drivers here though so that may not work. Also the design-time environment is always 32-bit. See https://technet.microsoft.com/en-us/library/ms141766(v=sql.105).aspx and other documentation.

In either case you may be able to break down your ETL process into smaller packages and chain them together with Execute Package tasks. With the "ExecuteOutOfProcess" option turned on each package called should be able to use its own 2Gb address space.

You might also try tweaking the way buffers are allocated in data flow tasks using the DefaultBufferMaxRows and DefaultBufferMaxSize properties; see here, here and here.


SQL Server 2016 Optimization I have two SQL Server 2016 Always On nodes - (VMware Virtual Machines). Each node has 250GB RAM, 46 vCPU @2.5GHz. Each of the MS SQL VM is dedicated to 1 ESXi Host so there is no resource contention.

The application that would connect to the Database is expected to be: 70% WRITE and 30% READ.

I need advise on the best way to:

Configure MS SQL nodes to use the 250GB RAM, 46 vCPU efficiently and optimally.
Perform Write and Read as fast as possible using all the hardware resources.

Configure MS SQL nodes to use the 250GB RAM, 46 vCPU efficiently and optimally.

Ensure that the NUMA configuration of the host is reported accurately by VMWare, and he out-of-the box configuration of SQL Server will do that.  Be sure to apply Service Pack 2 to get all the latest fixes.  There's some good stuff back-ported from SQL 2017 in there.

  Perform Write and Read as fast as possible using all the hardware resources.

The CPUs and RAM are only half the story when it comes to writing.

Your ability to Write will be gated by the performance of the disk containing your database log, and by your network connection to the AlwaysOn Secondary (assuming it's synchronous).

Consider Memory-Optimized Tables which are designed specifically to scale in write-intensive scenarios on big-memory multi-core machines by 1) minimizing logging, 2) eliminating locking and page latching, and 3) enabling native-compilation of TSQL code.

Your ability to Read should be largely unaffected by the disks, unless your frequently-read data is significantly larger than the RAM.


Why are Column Defaults on my User-Defined Table Type (UDTT) not being honored? I create a table type in SQL:

CREATE TYPE [dbo].[MyObject_TableType] AS TABLE
(
    [Name] VARCHAR(MAX) DEFAULT '',
    [Index] VARCHAR(MAX) DEFAULT ''
)

I build a DataTable and populate it with one DataRow. I provide that table as a parameter to a stored procedure:

// Set up connection and command variables (code omitted)
// ...

// Make the data table
var table = new DataTable();
table.Columns.Add("Name");
table.Columns.Add("Index");

// Add one row that is missing an Index
var row = table.NewRow();
row["Name"] = "ObjectOne";
table.Rows.Add(row);

// Make a parameter for the table
var tableParameter = new SqlParameter("MyObjects", SqlDbType.Structured)
{
    TypeName = "MyObject_TableType",
    Value = table
};

command.Parameters.Add(tableParameter);
command.ExecuteNonQuery();

I expected that inside of the stored procedure, the index value would default to '' (empty VARCHAR). Instead, I observed that the value was null. I know this because I attempted to merge the table type into an actual table, and the index column on my table does not allow nulls.

Why doesn't SQL Server honor the default that I placed on my table type? Is there a way to force SQL Server to honor it?

I think I can get around the issue by using the DataTable.DefaultValue property, but that solution has the disadvantage that I need to declare same defaults in two different places. I want to avoid redundant code if possible.

Why doesn't SQL Server honor the default that I placed on my table type?

Actually, SQL Server does indeed hono[u]r that default as the following test (using the UDTT provided in the Question) shows:

DECLARE @Test dbo.MyObject_TableType;
INSERT INTO @Test (Name) VALUES ('ObjectOne');
SELECT * FROM @Test;

Returns:

Name        Index
ObjectOne              <--- empty string, not NULL

The reason you are getting NULL instead of an empty string is because that is what you are asking the app code to provide. You even (unknowingly, of course ;-) identify the root cause of the problem when stating (emphasis added):

  I build a DataTable and populate it with one DataRow. I provide that table as a parameter to a stored procedure:

A DataTable is a representation of an actual Table in memory, not of one or more INSERT statements. So when you create that single row, the Index column can't be undefined: it is either NULL or some value. And when you pass that DataTable into SQL Server as a TVP, you are passing in the equivalent of a Table.

Yes, you could probably get around this by specifying a default value for that DataColumn in the DataTable. But I agree that doing this is not the best approach.

My preference is to never use DataTables unless you already happen to have one, regardless of using a TVP or not. I can't think of a reason to ever create one simply for the purpose of passing data into a TVP. Besides this particular issue, it also duplicates whatever collection you are placing into it simply to be a temporary transport for the TVP (which wastes memory and the time it takes to copy that data, even if it isn't much of either of those).

Instead, create a method that will take whatever collection you already have and simply stream it, one item / row at a time, into the TVP. You do this by returning IEnumerable<SqlDataRecord> from this method, and then specify this method as the Value property of the SqlParameter representing the TVP.

method to stream the collection into the TVP:

private static IEnumerable<SqlDataRecord> SendRows(List<DbObject> DbObjects)
{
   SqlMetaData[] _TvpSchema = new SqlMetaData[]
   {
      new SqlMetaData("Name", SqlDbType.VarChar, SqlMetaData.Max,
                      true, false, SortOrder.Unspecified, 1),
      new SqlMetaData("Index", SqlDbType.VarChar, SqlMetaData.Max,
                      true, false, SortOrder.Unspecified, 2)
   };
   SqlDataRecord _DataRecord = new SqlDataRecord(_TvpSchema);

   int _NumRows = DbObjects.Count;

   // read a row, send a row.
   // "for" should be faster than "foreach", right?
   for (int _Index = 0; _Index < _NumRows; _Index++)
   {
      _DataRecord.SetString(0, DbObjects[_Index].Name);
      _DataRecord.SetString(1, DbObjects[_Index].Index);
      yield return _DataRecord;
   }
}

using the method:

private struct DbObject
{
    public string Name;
    public string Index;
}

List<DbObject> _DbObjects = new List<DbObject>();

// Add one row that is missing an Index
_DbObjects.Add(new DbObject() { Name = "ObjectOne" });

// Make a parameter for the table
SqlParameter _ParamMyObjects = new SqlParameter("MyObjects", SqlDbType.Structured)
{
    TypeName = "MyObject_TableType", // not required for CommandType.StoredProcedure
    Value = SendRows(_DbObjects)
};

command.Parameters.Add(_ParamMyObjects);
command.ExecuteNonQuery();

The trick to getting the Default defined on the UDTT to populate the Index column with an empty string is the particular overload of the SqlMetaData Constructor that I used above. It has a boolean parameter for useServerDefault. Passing in true causes the column to not be passed into the INSERT statement if not "set" in the SqlDataRecord. Or, at least that is how I have always gotten IDENTITY columns in UDTTs to work (never tried it on an actual DEFAULT, but should be the same behavior).

P.S. Why are you using VARCHAR(MAX) as the datatype of these two columns in the UDTT? If either, or both, of these columns need to contain SQL Server identifiers (i.e. names of Tables, Views, Stored Procedures, Indexes, etc) then those are all sysname which is an alias for NVARCHAR(128). So you really need to at least be using NVARCHAR to avoid potentially data loss / hard-to-trace bug.


Postgres COPY with multiple small chunks In postgres, we can do COPY to export the data into file. But all the data will be saved into a file. But I want to chunk that file into small files. We can do this after the export.
But is there a way to export it during the COPY process?
OR in psycopg2 can we do this?

You can use a WHERE condition:
COPY (SELECT * FROM tab WHERE id % 3 = 0) TO '/dir/file1.csv' (FORMAT 'csv');
COPY (SELECT * FROM tab WHERE id % 3 = 1) TO '/dir/file2.csv' (FORMAT 'csv');
COPY (SELECT * FROM tab WHERE id % 3 = 2) TO '/dir/file3.csv' (FORMAT 'csv');

Here id stands for any numerical column. You can also use other data types and split up the data in other ways with an appropriate WHERE condition.


Azure SQL Database patch and update doubts It is known that Microsoft automates the patch and update of Azure SQL Database. But, some times applications must be tested before being applied any type of patch or update, because there's a possibility to get a failure in the application.

It's is possible for the customer to reverse a patch, or test it and authorize it before being applied?

Is it possible for the customer to reverse a patch, or test it and authorize it before being applied?

No.  It’s part of the service definition that updates won’t break your application.  Any potentially breaking changes, including query optimizer enhancements won’t be active until you opt in by changing your Database Compatibility Level.

See
https://docs.microsoft./en-us/sql/t-sql/statements/alter-database-transact-sql-compatibility-level?view=sql-server-2017


Changing IP Addresses of an Always On Availability Group in SQL Server 2014 We have two nodes in an Always On availability group, on SQL Server 2014 Enterprise.

We need to change the IP addresses of the nodes, the cluster, and the listener.

They will still remain in the same domain.

The subnet mask shall remain the same.

All will the be the same except the IP Addresses and the default gateway.

What are the steps to accomplish this?

Use the following steps to change IP address of the listener

ALTER ENDPOINT [MyEndpoint]
      STATE = STARTED
      AS TCP (LISTENER_PORT = 5023, LISTENER_IP = (10.x.x.x))
      FOR DATA_MIRRORING (ROLE = ALL, AUTHENTICATION = Windows Negotiate,
                          ENCRYPTION = REQUIRED ALGORITHM AES)
GO

http://www.ryanjadams.com/2016/01/change-availability-group-endpoint-ip/#axzz4yzaoZP8L

https://docs.microsoft.com/en-us/sql/sql-server/failover-clusters/windows/change-the-ip-address-of-a-failover-cluster-instance


PostgreSQL extending a variable-width bit string type? PostgreSQL supports Bit Strings,

SELECT B'01010101', B'01010101', '01010101'::bit(8), X'CC';
 ?column? | ?column? |   bit    | ?column?
----------+----------+----------+----------
 01010101 | 01010101 | 01010101 | 11001100

SELECT pg_typeof(B'01010101'), pg_typeof(B'01010101'), pg_typeof('01010101'::bit(8)), pg_typeof(X'CC');
 pg_typeof | pg_typeof | pg_typeof | pg_typeof
-----------+-----------+-----------+-----------
 bit       | bit       | bit       | bit

The type bit is typed such that I can create a function that accepts a bit of any length regardless (variable-length), and return a Bit String of variable-length:

CREATE FUNCTION type_test(_x bit)
RETURNS bit AS $$
  SELECT _x
$$ LANGUAGE sql;

SELECT type_test(X'CC'), type_test(X'CCCC');

Let's say I want to do an operation though that requires a bit-shifting algorithm, or a scratch pad bigger than the input type, how would I go about declaring that for internal use to the function. Like if I want to create a function that given X'CC' (8 bits) could use a 10 bit scratch pad.

This would almost do what I want,

CREATE FUNCTION type_test(_x bit)
RETURNS bit AS $$
  SELECT ('0' || _x || '0')::bit
$$ LANGUAGE sql;

SELECT type_test(X'55'); -- returns 0

But, it doesn't work because bit is bit(1) so the cast to it truncates the input to a single bit.

Given bit(n), how do I create a bit(n+2) to work with.

Using overlay

From the docs

  The following SQL-standard functions work on bit strings as well as character strings: length, bit_length, octet_length, position, substring, overlay.

PostgreSQL supports overlay on Bit Strings in two forms

overlay(bit,bit,int)
overlay(bit,bit,int,int)

You can use it like this,

CREATE FUNCTION type_test(_x bit)
RETURNS bit AS $$
  SELECT overlay(B'00' PLACING _x FROM 2 FOR 0);
$$ LANGUAGE sql;
SELECT type_test(X'ff');

 type_test
------------
 0111111110

Using "bit"

You can can also use the internal and undocumented "bit"

CREATE FUNCTION type_test(_x bit)
RETURNS bit AS $$
  SELECT ('0' || _x::text || '0')::"bit";
$$ LANGUAGE sql;

SELECT type_test(X'55');
 type_test
------------
 0111111110


PFS Page corruption issue and question We recently encountered PFS page corruption in our production DB.  We are in the process of checking old backups to find which ones are clean, but I was wondering if a different process would work to shorten the time needed to fix this issue.  So far we have not encountered any client issues, and the DB remains usable, but obviously need to fix the problem.I know we can't repair the individual PFS pages, but would the below work?

Msg 8948, Sev 16, State 6, Line 1 : Database error: Page (8:15390050) is marked with the wrong type in PFS page (8:15383376). PFS status 0x40 expected 0x60. [SQLSTATE 42000]
Msg 8948, Sev 16, State 1, Line 1 : Database error: Page (8:15390051) is marked with the wrong type in PFS page (8:15383376). PFS status 0x40 expected 0x60. [SQLSTATE 42000]
Msg 8948, Sev 16, State 1, Line 1 : Database error: Page (8:15390052) is marked with the wrong type in PFS page (8:15383376). PFS status 0x40 expected 0x60. [SQLSTATE 42000]
Msg 8948, Sev 16, State 1, Line 1 : Database error: Page (8:15390053) is marked with the wrong type in PFS page (8:15383376). PFS status 0x40 expected 0x60. [SQLSTATE 42000]
Msg 8948, Sev 16, State 1, Line 1 : Database error: Page (8:15390054) is marked with the wrong type in PFS page (8:15383376). PFS status 0x40 expected 0x60. [SQLSTATE 42000]
Msg 8948, Sev 16, State 1, Line 1 : Database error: Page (8:15390055) is marked with the wrong type in PFS page (8:15383376). PFS status 0x40 expected 0x60. [SQLSTATE 42000]

I used DBCC Page and discovered and all 6 pages belong to 1 table, so I am hoping this will work so we don't need to restore from old backups, but everything I've read said its either restore from backup, or repair allow data loss.  Also, what is the 0x60 status?  using DBCC Page shows the 0x40 status is Allocated, so i am curious what the status of 0x60 is.
What do people think about this process?  Would it work to clear the corruption?

Copy all data to a backup table - New data pages should be assigned, which would mean the PFS page entries for these pages should be clean.
Delete the source table - I assume this removes the bad PFS page entries since the table is no longer there...  But can SQL Server remove the errors at all if the PFS pages are corrupt, or would they remain even after deleting the table?
Rename the table back to the source table - Hopefully this will solve the issue since new data pages and new PFS page entries were created in step 2.

Has anyone attempted this or heard of ways to deal with PFS page corruption without going back to an old backup?

See https://www.sqlskills.com/blogs/paul/pfs-corruption-after-upgrading-from-sql-server-2014/
If this database was upgraded some time from SQL Server 2014 or earlier, you may have had the problem before the upgrade, but it was undetected by CHECKDB.
You should contact Microsoft Support, which will probably tell you to run a DBCC CHECKDB (or CHECKALLOC) with REPAIR_ALLOW_DATA_LOSS (if that's the only error, it should not cause data loss; it would only fix the PFS type). You may need to run that twice to fix it; after the first run, you may still have an error such as:
Page (1:111) in database ID 11 is allocated in the SGAM (1:222) and PFS (1:333), but was not allocated in any IAM. PFS flags 'MIXED_EXT ALLOCATED   0_PCT_FULL'.


Is xp_create_subdir supported on Linux SQL Server Some system extended procedures work on 2017 RC2

e.g.

exec xp_dirtree @directory = '/var', @depth = 1

Correctly returns the directories contained in /var

However:

exec xp_create_subdir '/foo'

Returns

Msg 22048, Level 15, State 0, Line 18
Error executing extended stored procedure: Invalid Parameter

Is the procedure supported, and if so what is the syntax for the parameter(s)?

Tested running on Ubuntu, inside Docker container on MacOS.

select @@VERSION
Microsoft SQL Server 2017 (RC2) - 14.0.900.75 (X64)   Jul 27 2017 08:53:49   Copyright (C) 2017 Microsoft Corporation  Developer Edition (64-bit) on Linux (Ubuntu 16.04.2 LTS)

According to the latest official release notes, no it is not supported.

The following features and services are not available on Linux at this time. The support of these features will be increasingly enabled during the monthly updates cadence of the preview program.
...
System extended stored procedures (XP_CMDSHELL, etc.)

It does say that there will be increasingly available but does not make any commitment toward a time or viability at release.


For each row, find the next closest value in another table (Teradata) I've seen a few close examples here but I'm not quite getting something close enough for me to noodle out a fix.

I have events, where trucks come in and out of a gate and I have two tables.  The first is all ingate events the second is all outgate events.  The problem is conceptually easy: For each ingate, find the nearest outgate.  The dilemma I have is that obviously trucks can come back.  So I might have 300 ingates for a unit on the ingate table with a corresponding 300 outgates.

Both tables look like this, except the other is session.Outgate

session.Ingate:

CHAS_INIT|  CHAS_NR   |     Ingate_TIME
---------+------------+---------------------
  MAEC   |   66862    | 2018-01-29-01.34.00
  MAEC   |   67218    | 2018-03-24-01.29.00
  MAEC   |   67557    | 2018-03-30-01.26.00
  MAEC   |   67557    | 2018-04-06-01.49.00
  MAEC   |   68773    | 2018-02-22-01.47.00

While this is a breeze to do in a spreadsheeting tool, I'm stumped on if it can be done during a join.  Any helps is greatly appreciated.

select CHAS_INIT, CHAS_NR, Ingate_TIME,
   -- find the previous IN-time
   last_value(case when inout = 'i' then Ingate_TIME end ignore nulls)
   over (partition by CHAS_INIT, CHAS_NR -- does this determine a truck?
         order by Ingate_TIME)
from
 (  -- combine both in & out into a single result
    select 'i' as inout, CHAS_INIT, CHAS_NR, Ingate_TIME
    from session.Ingate
    union all
    select 'o' as inout, CHAS_INIT, CHAS_NR, Ingate_TIME
    from session.Outgate
 ) as dt
-- only return OUT rows
qualify inout = 'o'


Easy or Schedulable way to move data from Production to Test What I am looking to do, is schedule a copy of data from my production database to my dev/test database.

The dev/test database will be newer than the production database in terms of schema, but the production database has current data. I'm in a bit of a bind as I need to test my database changes against production size data, and it would be very helpful to have current data.

I'm on SQL Server 2008 R2 Standard for both production and development environments; is there a way I can create a job on my development server to "suck" data from my production database in a read-only fashion? I'd like a script that can copy table data wholesale, ignoring missing columns and ignoring any constraints in the target table. A script that does this for one table is all I really need, I can modify it to fit my tables and duplicate it to run for all my tables. The issues I'm having is finding anything even remotely similar to this.

This is a bit of a follow-up on my last question of the same nature; I have since got my database into source control, and I'm liking it much better. The problem is that I still face a stale data issue on my development server, so I'd like to find a way to schedule updates.

I'm open to alternate ways to achieve this, but it needs to be something I can run on a schedule and something I can build with notepad and/or SSMS or other tool available with Standard Edition SQL Server.

Here is what I would recommend:

If you do not already do this, create a regular backup process for your production database. To make life simple, make it a full backup and include all database objects (tables/schema/data/users). The Microsoft website has a lot of information on how to achieve this.
When you need to update your development environment, restore the production backup into development, then implement the migration (tables, procedures, data) you need to test using the method that is documented for the production change.
Test, knowing that you are not just testing your code changes, but also testing your migration strategy for when you roll out your change to the production environment. You are also testing your database backup and restore (DR) strategy as well.

Note, this may mean you have 2 development databases, one for actual development and one for integration/QA/UAT testing.


How to deal with data repetition? I am unsure if this has been asked already. Tried searching but couldn't find one.
I'll explain with a close example (I call it the tags problem). Consider a basic CMS. A post can have many tags and should have at least one. A tag can have more than one words. A tag, when entered by the user, where they can either select from a list of tags and if tag a tag is not in the system, it has to created by the user.
Now the main problem is replication. There are thousands of tags. Many tags get repeated because of their definition of tag (Eg: let's say a post should have a tag called organisation, but the user adds institution as a tag.). Some of them are spelling mistakes or plurals (like Afforestation, Afforestations and multiple afforestation).
This leads to some unfavorable analytic reports which is one of the main output of the system.
Some solutions I can think of,

A separate responsible user needs to add a tag. Problem with this is, human intervention (small factor), the user has to wait till the tag get approved, and when the amount of tags increase, that user will be unable to track/remember which are new tags.

The user will add the tags, and then a person has to review those tags and edit/merge them. Eg: A user adds a tag called dams, when there is already a tag called dam. This new tag will be accepted by the system and other users can also view/use those. But then a responsible person then can review the tag and have the ability to update the tag in all places where its used to appropriate tag. Same problems as above except one is solved.

What is your approach? How to deal with this kind of replication?
Apology if this is the wrong stack-exchange.

Probably this question is more suitable for StackOverflow ;-)
Anyway: a combination of the two approaches is what I'd use: the User has a small research tool that displays the tags that contains the word (or a part of the word) User is inserting. If a tag already exists the User can select that tag. If a tag doesn't exist, the new tag will be manually approved by a "superuser".
I think this looks like the same approach used in StackOverflow (and in all forums of Stack Exchange)


How to point Azure Hybrid Connection to Availability Group Listener ? Or redirect to secondary replica? We have 2 SQL Servers on-premise, combined into Availability Group

We want to install windows updates, which will most likely require a reboot of virtual machines, and we are going to use rolling update technique (update secondary replica; fail over to secondary so it becomes primary; update former primary)

Applications that are located on the on-premise web server, are going to be fine, since web-servers' connection strings are pointed to AG Listener

But we also have apps running in Azure,
which are connecting to on-premise DB (to primary DB replica directly) using hybrid connections.
Azure Apps are going to be down if primary replica is down

Question: is there any way that we can point Azure hybrid connections to AG listener ? Or at least redirect hybrid connection to secondary replica if primary is not available for 1-2 mins ?

You should be able to connect to the AG listener  from Azure to On-premises or vice versa--though it's not as straight forward as you might think. Here's one of the best article's I've ready today on how to do it.

https://www.concurrency.com/blog/w/creating-a-listener-for-a-multi-subnet-hybrid-or-a

When the AG primary node is on-premises (or in Azure), you should be able to connect to the listener. So you're relying on Azure DNS to do it's job properly and resolve your SQL Azure node correctly. Haven't tried it myself--sounds awesome!


What's the most cost effective or cheapest way for high availabity on Oracle Standard Edition One? We are currently using Oracle Standard Edition One running on two physical database server.

If I wanted to get higher availability, what's the most cost effective way or cheapest way to achieve this?

Thank-you for your suggestions!

With dbvisit you can maintain a standby database, in a very similar way as Data Guard does in the Enterprise Edition. You might want to check this out. The setup is very easy, support is good and the pricing is very reasonable, if not, cheap.
I blogged about it a few times,

see how to configure dbvisit to manage existing standby database running SE

and Cost reduction using Oracle SE and dbvisit standby

I hope this helps,
Ronald.


Nice Database GUI for Lots of Text I am designing a workflow processing system. I have many (hundreds) of workflows that in many cases are variants of each other and have related characteristics.
In the beginning development stages I need to document these workflows in a way that less-technical personnel can interface with. They break down their usage scenario and document it all in one place, the dev team will go through later and write code to implement their descriptions.
All I need to do is make a giant spreadsheet with each row containing a single workflow specification, with columns such as "Inputs", "Outputs", "Process Description", and "Implementation Status."
The Process Description field will contain several paragraphs and ordered lists describing the potentially intricate process and any nuances/gotchyas in human readable form.
Typing all of this information into an Excel spreadsheet is tedious and people keep hitting Enter instead of Shift+Enter. Reading it requires expanding the row or column dimensions, which makes scrolling through to quickly check the status intractable. Keeping the dimensions small makes entering data hard. Cutting and pasting blocks of text to break apart and reorganize the descriptions is...you guessed it, beyond unintuitive.
Has anyone come up with a solution with a good UI that can store information like this? And has a collapse-all/expand-all toggle?
Maybe I should roll-my-own database-backed web app...Is anyone interested in working with me to develop this as a product?
Of less importance but still kind of pretty important, is the relationships. For example Workflow 302 might use the same inputs and outputs as 303, and the process description needs only a few, very critical, words to be changed.
I feel like MS Access would be along the lines of what I'm looking for to potentially support data relationships; however once again being able to type a few nicely formatted paragraphs or a page of text within one cell needs to be a painless process.
Thank you for any suggestions!

The data itself is a very simple data problem, and any modern database system will work just fine (though I recommend staying away from MS Access just because it's antiquated as a database system nowadays).
The actual problem you're trying to solve is a UI one, which you'll want to likely provide a rich text editor for the end users to type their instructions (ProcessDescription) into. This can be accomplished easily with any desktop or web application, which there are a multitude of rich text editors out there for (dependent on whichever front-end / back-end stack you choose). The rich text editor will usually store the formatting of the text with special code (most times HTML tags or something similar are used) behind the scenes from the end user, which will be completely digestible by your database without any special extra work needed to store it and recall it in the same format.


Deployment Manager Vs DBA Cage Match On Pushing Releases To Prod Our company has now hired a full time release engineer for the MS Windows side and we were thinking of a release management process for MS SQL Server.

IF we have confidence in their abilities to deploy SQL Server scripts, is it common to just have the release manager do it or is it something that the DBAs typically do?  Our enterprise is growing fast and our DBA's are somewhat overloaded (surprise surprise what else is new) but we can hire more, but that's another matter.

How do you guys manage this?  Do you allow a release manager to have access to prod to roll out changes?  Do you take away rights and turn them on when they need to release?  I'm thinking I will give them access to a sproc that gives them prod access for an hour, but it logs who calls it.

Or am I totally off, and this is something a DBA should always manage?

Any thoughts would be appreciated!

Edit:

Update:
Also what happens when we encounter anomoloies?  For example, a dev stated that 'these tables should match this other environment (by environment I mean customer prod environment, not qa/stage/etc.)'.  Usually they would do a spot check.  I did a checksum and noticed issues which ended up being just whitespace issues.  In a case like this, do we push it back to the release manager/qa person to fix after doing basic troublehshooting?

Another example:  We have scripts by about 20 developers, sometimes they are dependent on each other.  The ordering of the script was wrong.  I can't keep up with 20 developers work and also manage the data, but after some troubleshooting we discovered the issue and changed the order.  Is this something the DBA should typically be deeply involved in or is it fair after basic testing and look over, we send it back to the release manager and devs to fix?

Firstly, every environment is different. What works in my environment won't automatically work in yours. (We = DBA Team)

In mine we have the following environments for this process

Dev - Normally we restore a copy of live in preparation for a release and sanitise data as required. We then back that up and enable the dev to restore to that backup at will. They then prepare and test their deployment scripts. We expect the Devs to have tested the upgrade/release process in full successfully before it needs to go to UAT. We will often be required to help and advise at this stage

UAT - Again we would restore the copy of live and sanitise (if we are going to use a QA environment). The release and config manager then provides us with the correct scripts for the upgrade from the developers and we will check them, run them whilst monitoring to be able to provide all info required for CAB regarding loss to service, time to upgrade and any changes required to maintenance/monitoring scripts which we then advise the Release Manager.

Sometimes we use a QA environment, usually when sanitised data has been used in the two environment above and the business are required to QA rather than the test team.

CAB - The Release Manager provides all the information for CAB to make their decision from the proceeding steps to lay out exactly what happens during the release. They are also responsible for storing the scripts used to go from Dev to UAT and providing them to us for release into live. We only use the scripts provided by the Release Manager and referenced in the CRP. We obviously check that the scripts are the same as the ones we used in UAT as I am ultra cautious

Live - We deploy to live exactly following the steps in the Change release Plan authorised by CAB, often co-ordinating with other teams

The reason we do it like this is partly as it is the process that we have to follow here (Using CAB etc) but in answer to your question it is because the DBA team are the ones responsible for the safety and accessibility of the data. Also because we have the skills, knowledge and experience to recover from unexpected problems. What happens to the Live databases is our responsibility and we take it very seriously. No-one else is allowed to perform tasks on the live databases other than the DBA team. We are the guardians and the bouncers (door men/women)

For your update. I bounce that sort of issue back to the developers and require them to fix it before I deploy to UAT. Once the script is finalised (at UAT) we know it will work in live.

For your another example - That is the job of the release manager. If they are providing us with scripts that fail we bounce it bak to them and require them to fix it before we will deploy into UAT. We WILL NOT deploy into UAT anything that requires bodging or changing from the instructions provided and in the few occasions wen this has happened (a developer used the app account to manipulate some data after we had run the scripts to enable the release to pass UAT) I stand up in CAB and explain this and explain that I am not happy to release this to live without a correct test of the release. This usually works :-) I know I am harsh but I am also fair.

I tell the release managers and the devs that the process of passing the release to UAT is the test not only of the release but of how the release will be performed therefore we will follow the exact same steps in both examples (dev - UAT and Live) and that if it fails in UAT it goes back to them to fix.

I hope that helps


multiple MYSQL full text indexing for innodb and memory + disk efficiency I have the following table: (Create table statement)

CREATE TABLE `phppos_people` (
  `first_name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `last_name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `phone_number` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `email` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `address_1` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `address_2` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `city` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `state` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `zip` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `country` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `comments` text COLLATE utf8_unicode_ci NOT NULL,
  `image_id` int(10) DEFAULT NULL,
  `person_id` int(10) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`person_id`),
  KEY `phppos_people_ibfk_1` (`image_id`),
  FULLTEXT KEY `full_search` (`first_name`,`last_name`,`email`,`phone_number`),
  FULLTEXT KEY `first_name_search` (`first_name`),
  FULLTEXT KEY `last_name_search` (`last_name`),
  FULLTEXT KEY `full_name_search` (`first_name`,`last_name`),
  FULLTEXT KEY `email_search` (`email`),
  FULLTEXT KEY `phone_number_search` (`phone_number`),
  CONSTRAINT `phppos_people_ibfk_1` FOREIGN KEY (`image_id`) REFERENCES `phppos_app_files` (`file_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci |

I have requirements for a fast search using first name, last name, email and phone number at once; but also being able to search each individual field. I also like the relevancy sorting of full text which cannot be done with Btree

The queries work well but according to my cloud database provider they recommending btree indexes for better memory and disk usage.

Do I have any other options/configurations or way to create indexes that don't use a lot of disk + memory? I have about 40 full text indexes per database that follow a similar pattern. Performance is great with these indexes and has really sped up product.

Is Innodb full text designed to use a lot of memory + disk? Here is my mysql variable settings

https://gist.github.com/blasto333/aa4241a4e37447961188356719ea6984

Here are some stats from my provider

Upon review was getting an idea of the actual data set size as stored within InnoDB.  It was a bit of a surprise when I found your dataset size much smaller than your on disk usage as listed below:

Such a discrepancy between the on disk and tablespace usage had me concerned initially that we had something orphaned, such as old temp tables, but later found this was being caused by a heavy usage of InnoDB Full Text indexes.

PHP-POS-DB-01
  On Disk:                              18GB
  Total DataSet Size:                   4.56GB
  Unused allocated Tablespace:          3.10GB  ## Fragmentation
  Total innodb used plus fragmentation: 7.66GB

PHP-POS-DB-2-01
  On Disk:                              9.7G
  Total DataSet Size:                   923.85MB
  Unused allocated Tablespace:          363.00MB
  Total innodb used plus fragmentation: 1.29GB

first_name has one word, correct?  FULLTEXT(first_name) is not a use case for that type of indexing.  I recommend you use simpleINDEXes` for any simple columns.

This works very efficiently, probably faster than anything that could be done with FULLTEXT:

WHERE last = 'Muench' AND first LIKE 'C%'
INDEX(last, first)

You might still want FULLTEXT(first, last, email, phone) for handling the case where any of those might be entered.  (However, it is unclear whether this is a good use case.)

Meanwhile, don't blindly use (255) for all string columns; use sensible limits.

It is often wise to have the UI construct the WHERE clause based on what the user does/doesn't supply.  In your case, this might include switching between MATCH..AGAINST, =, and LIKE, maybe even REGEXP.

You could also look for @ to discover emails, all-digits to discover phones, etc., thereby avoiding searching lastname for foo@bar.com.

As an alternative, you could add an extra column that contains all the 'words' for name, email, etc.  Then have a single FULLTEXT index on that one column; no need for 40.  The drawback is the need to maintain this redundant info.  With a new enough MySQL or MariaDB, it could be a 'generated' column.


Does Express Profiler have a major effect on performance of SQL Server Express I'm wondering does running Express Profiler against an SQL Express Server have a major impact on performance.  The versions of SQL Express I'm looking to run against are on 2008 and 2013.

This is the express profiler I'm using. http://expressprofiler.codeplex.com/

Performance impact of expressprofiler is almost the same as of standard sql profiler and can be up to 25%


will this insert query take too long to run? I am bulk inserting from a node server to a psql database by constructing the query like

let query = `INSERT INTO ${table} (timestamp, lattitude, longitude, type) VALUES `;
locationData.forEach(location => {
    query += `('${location.timestamp}', '${location.lattitude}', '${location.longitude}', '${type}'), `;
});

this api gets called every 3 minutes by a device that logs location every second, meaning atmost 180 rows will be inserted in each query. will it be a bottleneck?

As the others have said 180 rows is not a big deal.

That you are forming the 180 rows into a single insert command is a good thing, it will reduce both latency and the workload on the database.

Any computer capable of running Postgresql should have no problem with that work-load. this includes NAS boxes, cell-phones, and Raspberry Pis.


To get the first author of the first book, you can use

select ExtractValue(xml_doc, '//book[1]//author[1]');

To get a real distinct list you would have to write a stored function that circles through all books and all authors.


SSMS "New Query" Behavior On my desktop and on most other instances of Sql Server 2008 R2 and 2012, the default behavior when you right click on a database in the solution explorer of SSMS and choose "New Query" is a new query window opens and changes the database context to the database you clicked on. If you click new query button from the query window, it will open a new query from the same database.
However, on some other machines, including my boss', the new query opens but the context is the default database (master by default). Clicking new query will likewise open a query in the default database. What is the option to make his instance act like mine?

If I connect to his database from my ssms, it still behaves like it does on his machine (incorrectly).

Max Vernon's suggestion about scripting the logons is spot on. For generic Windows Authentication, the default database tends to be master. Setting a default database for each database instance/logon combination will allow control over what the default database is when launching a new query editor window in SSMS.

If you use master for windows auth and/or administer many SQL Databases, the CTRL+U shortcut from a new query window -> select database -> hit enter to select and return to query editor will become firmly ingrained in motor memory.