SQL Server Configuration Manager not display services Config: Window Server 2008 R2 Enterprise SP1 / SQL Server 2008 R2 Enterprise (RTM)

I have searched through Google using many different words and phrases. I know this was a common issue to occur with SQL 2005 and most mention doing a command line to re-register the mof file and a few other things.

I'm getting the same issue similar to what everyone else receives: I can only see SSIS service within SSCM.

I have checked Wmimgmt.msc permissions and I am an in local Administrator group and that group has Remote Enable. Reference
I have enabled WMI trace log (feature in Server 2008 Event Viewer) and see the calls being made to the WMI provider, but no errors showing. Just information messages.
I have tried calling the services information through PowerShell WMI command (here) and it only returns SSIS service as well. No error is returned when I execute the $mc.Services | Select Name, DisplayName it just returns SSIS service info and that's it.
I've gone through the GP policies and don't see any settings that would restrict me having access to WMI providers.
I have permission to "..\Microsoft SQL Server\100\Shared\sqlmgmprovider.dll" file.

I'm officially stumped now. I also checked Microsoft Connect and did not see any thing for this issue. SP1 for SQL Server 2008 R2 has not been applied but I don't see anything in that documentation that would have any effect on this issue.

I know of one other thing that you can try.  It affects SQL Server 2005 specifically, but might also be useful in your scenario.  Essentially, the wmiprvse.exe executable runs under the context of NETWORK SERVICE.  SSCM loads the Sqlmgmprovider.dll into this executable under the same security context.  Ensure that NETWORK SERVICE has READ permissions to the SQL Services in question.

The steps needed to accomplish this are detailed in the Microsoft Support Article:

Some or all SQL Server 2005 services are not listed in SQL Server Configuration Manager, or you receive a "No SQL Server 2005 components were found" error message when you perform operations in SQL Server 2005 Surface Area Configuration.

To do this in a standalone environment is a bit trickier.  Let's try this with SQL Browser first:

Open cmd.exe as an administrator.
Type the following command:  sc <server_name> sdshow <service_name>
Copy the string returned to your favorite notepad clone, we will need this string to revert back to prior configuration if this does not work.  This is our SDDL string.  The next step will be our concatenated SDDL string.
Add the following to the end of the string:  (A;;CCLCSWRPWPDTLOCRRC;;;NS)
Type the following command:  sc <server_name> sdset <service_name> <concatenated_SDDL_string>
Open SSCM and see if SQL Browser displays as expected.

The two commands run should look something like this:

sc \\my-server sdshow SQLBrowser

sc \\my-server sdset SQLBrowser D:(A;;CCLCSWRPWPDTLOCRRC;;;SY)(A;;CCDCLCSWRPWPDTLOCRSDRCWDWO;;;BA)(A;;CCLCSWLOCRRC;;;AU)(A;;CCLCSWRPWPDTLOCRRC;;;PU)(A;;CCLCSWRPWPDTLOCRRC;;;NS)


mysql query with 1 derived table and 1 inner join takes forever to load I tried removing some chunks of code little by little and found out that the condition inside the inner join is the devil. Can someone enlighten me on how to achieve this when I have like 10,000+ rows of data. This works perfectly fine if I have 1-50 row of data

SELECT negotiations.id
FROM
(SELECT n.id,n.business_rank,db.status,
case status
        when '11' then IF(DATEDIFF(IFNULL(apply_date, curdate()), curdate()) < 1, true, false)
        when '12' then IF(DATEDIFF(IFNULL(db.contract_date, curdate()), curdate()) < 1, true, false)
        when '13' then IF(DATEDIFF(IFNULL(db.settlement_date, curdate()), curdate()) < 1, true, false)
        when '20' then IF(DATEDIFF(IFNULL(db.apply_cancel_date, curdate()),  curdate()) < 1, true, false)
        when '21' then IF(DATEDIFF(IFNULL(db.contract_cancel_date, curdate()),  curdate()) < 1, true, false)
end as to_notify
FROM negotiations AS n
INNER JOIN deal_buildings AS db ON db.id = (
    SELECT id
    FROM deal_buildings AS db2
    WHERE db2.deal_id = n.id
    AND n.main_member_id = 79
    AND db.status in (11,12,13,20,21)
    ORDER BY status DESC
    LIMIT 1
) ) as negotiations
WHERE ((
(business_rank = '17' and status = 11) or
(business_rank = '19' and status = 12) or
(business_rank = '22' and status = 13) or
(business_rank = '18' and status = 20) or
(business_rank = '20' and status = 21)) is false )
and to_notify

(Need formatting not available in a Comment.)

This is ambiguous:

FROM deal_buildings AS db2
WHERE db2.deal_id = n.id
  AND n.main_member_id = 79
  AND db.status in (11,12,13,20,21)
ORDER BY status DESC              -- Is this in `db` or `db2`?  QUALIFY!

IF(blah, true, false) can be shortened to just (blah)


Postgres not using multicolumn index for queries on leftmost columns? I'm working in Postgres 9.5.15 and have a table called project:

 id                             | integer                     |           | not null | nextval('project_id_seq'::regclass)
 user_id                        | integer                     |           | not null |
 is_deleted                     | boolean                     |           | not null | false
 is_listed                      | boolean                     |           | not null | false

And it has this multicolumn index:

"project_is_deleted_is_listed_user_id_ix" btree (is_deleted, is_listed, user_id)

But if I try to query the two left columns, the table is being sequentially scanned rather than using the index:

LIVE !!! => analyze;
LIVE !!! => explain analyze select * from project where is_listed and not is_deleted;

Output:

    QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------
 Seq Scan on project  (cost=0.00..36846.95 rows=460760 width=508) (actual time=0.006..281.478 rows=394615 loops=1)
   Filter: (is_listed AND (NOT is_deleted))
   Rows Removed by Filter: 491652
 Planning time: 0.091 ms
 Execution time: 356.221 ms
(5 rows)

I don't understand why this is - 356ms execution time seems quite slow, surely it'd be faster to use the index? And given that the two left-most columns are being queried, surely the index is usable?

Your query is returning half of the table.  There is little point in using an index in such a case.  Why do you think it would be faster to read both half the index and the table, rather than reading just the table?


Maatkit shows the MySQL replication error in one table, but won't fix it We use MySQL 5.1 with a primary/secondary replication setup. I use mk-table-checksum from Maatkit to generate checksums on the master and perform consistency checks on the replica.

mk-table-checksum is used to generate the checksums, and is pretty simple:

% mk-table-checksum localhost \
        --quiet \
        --replicate=test.checksum --empty-replicate-table --create-replicate-table \
        --chunk-size=500000 --sleep-coef=0.5 \
        --ignore-tables=mysql.general_log_backup,mysql.general_log,mysql.slow_log,mysql.help_topic,mysql.tables_priv

These checksums are then replicated to the Replica (slave) server, where we compare the checksums.

Around a month ago we had a server failure. We fixed the server, started up the database and resumed replication. Everything seems to be working fine-- Slave_IO_Running and Slave_SQL_Running are both set to "Yes", everything appears to be getting replicated successfully except for the error mentioned below, etc.

There is one problem, however. When I check the table using the following recommended method on the Replica, I see a consistency problem.

# mysql --execute "SELECT db, tbl, chunk, this_cnt-master_cnt AS cnt_diff, \
>         this_crc <> master_crc OR ISNULL(master_crc) <> ISNULL(this_crc) AS crc_diff \
>         FROM test.checksum \
>         WHERE master_cnt <> this_cnt OR master_crc <> this_crc OR ISNULL(master_crc) <> ISNULL(this_crc);"
+--------+------------------+-------+----------+----------+
| db     | tbl              | chunk | cnt_diff | crc_diff |
+--------+------------------+-------+----------+----------+
| plants | trees_properties |    40 |        0 |        1 |
+--------+------------------+-------+----------+----------+

This problem still occurs even after force another sync on the master using mk-table-sync on the master. I will probably need to replace the corrupt data by hand, but I am unclear what the actual problem is. What is a 'chunk'?

My guess is that only a handful of rows are different, and I can probably replace those rows by hand. But how can I list which rows are missing?

I realize that Maatkit has been deprecated in favor of Percona Tools. I plan to upgrade some day. But for now, "if it ain't broke, don't fix it."

I think a chunk is a set number of rows to be scanned and checksummed.

I never did like chunks because if the table order matters, each chunk could be completely different if just one row was written ahead or behind another.

Try not setting a chunk size and see if the entire table is checksummed as a whole as a opposed to each chunk checksum.

You can also run mk-table-sync using the --print option and redirect to a text file to see if there is any SQL created that would have been executed to sync the slave had you used the --execute option. If nothing comes out into the text file from mk-table-sync, you will have to resort to doing physical copying (for MyISAM) or mysqldump the table and loading it on the slave (for InnoDB).

Give it a Try !!!


AG listener issues with ApplicationIntent=ReadOnly Need help in resolving a specific issue connect AG listener with ApplicationIntent=ReadOnly. We have 3-node cluster with AlwaysOn AG and synchronous replica. All 3-nodes are in same data center (Rackspace Dallas). I have verified that:

We are connecting to Listener Name.
The Read-Only routing list (READ_ONLY_ROUTING_LIST) is defined.
The Routing URL (READ_ONLY_ROUTING_URL) of each instance has a proper FQDN and port combination.
ApplicationIntent is specified in the connection string.
The Sync_State is SYNCHRONIZED or SYNCHORNIZING for the secondary replica.
The secondary replicas are set to allow connections.
The initial catalogue is provided in connection string.

The AG listener with read-only intent connections works fine if I RDP into the servers and connect from any one node to the AG listener via SSMS. We host our servers in Rackspace. However, if I use the outside network (from my office location) to connect to AG listener (using public facing IP), the regular connection works fine but not the readonly intent ones.

Ruled out drivers and SSMS version issues as I checked this on SQL server 2008 R2, 2012 and 2016 with respective SSMS versions. .NET run time on all machine is 4.0.3+ so that is not an issue.

All 3-nodes are connected via a private switch for replication (192.168.5.x subnet), and all 3-nodes are accessible via public facing IP (192.168.99.x subnet through firewall) as well. Can access all of the nodes via respective IPs from our office location (outside Rackspace). The AG listener is configured to respond on (192.168.5.x and (192.168.99.x) for redundancy. Can ping one node to another via HostName, IP and FQDN as well.

The only thing that is missing is a DNS entry for the AG listener, which I am going to create to rule out the issue. However, I am connecting using IP address from our office location to the data center.

The reason that this fails outside of the internal Rackspace location is due to the URL Endpoints being set to a value that is not able to be connected through from your local environment.
I discuss this process at a high level in this blog post, however to quickly recap the point that needs to be made here:
The endpoint url is the address where the connection will be routed in order to connect and run their queries. [roughly speaking]

The URL is active directory specific FQDN, so I cannot use it as is
from ourside the the racksapce domain. The ROR URL is some thing like
"tcp://1234-db1.abc.intensive.int:1433"

This means that the client driver is going to be re-pointed to this address; if the address is not reachable then the driver won't be able to connect and you'll have a problem - which you happened to run into.

In your example, the client driver will get back
"tcp://1234-db1.abc.intensive.int:1433" and attempt to connect to it like it would any other instance of SQL Server. Since it can't (as
you've stated) you won't be able to be routed and your connection
should then be on the primary.  What I did was I changed the ROR URL
for each node to public IP address instead of the FQDN/ Host-name, so
when the request is sent to AG listener from outside it knows where to
hand-off in the public facing IP. It looks like, if we have to connect
from outside I have to use public IP (Eg: 72.32.XX.XX) as opposed to
tcp://1234-db1.abc.intensive.int:1433 in the ROR.

While the IP may or may not need to be public (different architectures at different companies' may or may not require different things), it seems in your case it does... though I'm not a networking architecture guru so I can't comment on how/why this was implemented or any technical challenges observed.

Also when we deploy the application in the same domain as the db
servers Active Directory domain "abc.intensive.int" would that pose a
problem when apps try to connect to the db AG listener?

The domain shouldn't make a difference here, the real difference is whether or not your DNS is setup where it can do the lookups required to get the information (IIRC this would be a reverse lookup zone but don't quote me on that). If you can resolve the DNS name, it will work fine, if you can't it obviously won't... this is more of a question for your DNS/AD admins as they hold all the information to that kingdom.


What to do with WAL files for Postgres Slave reset So last night our PG Slave ran out of space after a lot of reconfiguring of disk space, new drives etc its now reporting the following error:

FATAL:  could not receive data from WAL stream: FATAL:  requested WAL segment 00000001000018F70000008A has already been removed

From the reading around this I've done, it appears that the only solution is to re-sync the slave with pg_start_backup() et al. Based on this, I have a few questions.

Is there a better way of fixing the slave that I've simply missed or overlooked?
Do I need to clear out the WAL files on the slave and/or master prior or during the backup?
Does pg_start_backup lock the database during this time?

As requested, the log file can be found: http://pastebin.com/9F8vJh6R, have removed the rest of the file as its just 5 hours of the same repeated error

Many thanks

If you have WAL archiving enabled on the master (archive_command is set and archive_mode is on), set a restore_command in your replica's recovery.conf to allow it to fetch WAL from the WAL archive.

If there's no WAL archive, then there's no record of needed deltas between the master and the replica anymore. So you must resync them.

Typically this is done by making a new pg_basebackup of the replica. If the replica is big, though, it can be helpful to use rsync to resync the replica from the master doing block compares. To do this, you:

pg_start_backup() on the master
Stop the replica if running
rsync the master to the replica
pg_stop_backup() on the master
Copy any additional files from pg_xlog on the master to the replica, up to the file reported by pg_stop_backup()
Start the replica

It's simpler if you have WAL archiving enabled, since you then don't have to manually copy WAL, you just set a restore_command on the replica.

All sound too complicated? Use pg_basebackup.

As for your other questions:

NEVER delete WAL from the master. Ever. Extremely bad. Hands off pg_xlog.
pg_start_backup doesn't "lock" the database. It does prevent VACUUM from cleaning up dead rows, so it can increase bloat on high write activity tables, but that's about it.


Efficient way to get data for each date range intervals using single query | MongoDB I have collections about products and purchases. I need to get statistics for each time interval.
I need following:

Name
quantity (date 1 range)
quantity (date 2 range)

Name One
500
400

Name Two
251
450

Name Three
444
600

I have following collections:
//Products
{
 name: {
    type: String,
    required: [true, "The prodcut name is required"],
    unique: true,
}
price: {
    type: mongoose.Types.Decimal128,
    default: "0.0",
  },
}

//Orders model scheme
 {
   product: {
    type: Schema.Types.ObjectId, ref: "Products",
},
price: {
    type: mongoose.Types.Decimal128,
    default: "0.0",
},
quantity: {
    type: Number,
    default: 0,
},
orderDate: {
        type: Date,
        default: Date.now, //new Date(),
    },
  }

And I have the following aggregate function:
    OrdersModel.aggregate(
        [
            { $match: { orderDate: { $gte: new Date( sdt ), $lte: new Date( edt  ) } } },
            {
                $group:
                {
                    _id: "$product_name", //{ name: "$name"},
                    totalQuantity: { $sum: "$quantity" },
                }
            },
            {
                $project: {
                    _id: 1,
                    qty: "$totalQuantity",
                    //...
                }
            }
        ]
    )

This query is working, but only for one time interval. But I need to get results for multiple time intervals.
I'm new to NoSQL, I've tried retrieving data from one or more-time intervals, but I couldn't find out how to achieve this. How can I solve this problem?
I need to get the sales total Quantity of a product at different time intervals.
2010-01-01 to 2012-01-01
2015-01-01 to 2017-01-01
...

Time intervals can be dynamic. There can be a minimum of 1 and a maximum of 4 time intervals.

If you have only 4 intervals then this could be an approach:
OrdersModel.aggregate([
   {
      $facet: {
         interval1: [
            { $match: { orderDate: { $gte: new Date(sdt_1), $lte: new Date(edt_1) } } },
            {
               $group:
                  {
                     _id: "$product_name", //{ name: "$name"},
                     totalQuantity: { $sum: "$quantity" },
                  }
            },
            { $project: { _id: 1, qty: "$totalQuantity", } }
         ],
         interval2: [
            { $match: { orderDate: { $gte: new Date(sdt_2), $lte: new Date(edt_2) } } },
            {
               $group:
                  {
                     _id: "$product_name", //{ name: "$name"},
                     totalQuantity: { $sum: "$quantity" },
                  }
            },
            { $project: { _id: 1, qty: "$totalQuantity", } }
         ],
      }
   }
])

A more dynamic way would be this one:
var facets = {};

facets["interval1"] = [
   { $match: { orderDate: { $gte: new Date(sdt_1), $lte: new Date(edt_1) } } },
   {
      $group:
         {
            _id: "$product_name", //{ name: "$name"},
            totalQuantity: { $sum: "$quantity" },
         }
   },
   { $project: { _id: 1, qty: "$totalQuantity", } }
];

facets["interval2"] = [
   { $match: { orderDate: { $gte: new Date(sdt_2), $lte: new Date(edt_2) } } },
   {
      $group:
         {
            _id: "$product_name", //{ name: "$name"},
            totalQuantity: { $sum: "$quantity" },
         }
   },
   { $project: { _id: 1, qty: "$totalQuantity", } }
];

facets["interval3"] = [];
facets["interval3"].push({ $match: { orderDate: { $gte: new Date(sdt_3), $lte: new Date(edt_3) } } });
facets["interval3"].push(
   {
      $group:
         {
            _id: "$product_name", //{ name: "$name"},
            totalQuantity: { $sum: "$quantity" },
         }
   },
   { $project: { _id: 1, qty: "$totalQuantity", } }
);

OrdersModel.aggregate([
   {
      $facet: facets
   }
])


Query with dynamic ranges in PostgreSQL based on status We have a table with invoices classified per day, with normal invoices and void invoices (invoices that are voided, in our country we use the Spanish term "anuladas"). For example suppose that we have the next list of invoices for today (where APL stands for Applied and ANU for Void or Anulada):

ID     TOTAL       STATUS     DATETIME            INVOICE_NUMBER

1      00.50       APL        2014-07-04 07:35    1355
2      10.50       APL        2014-07-04 07:36    1356
3      12.50       APL        2014-07-04 07:38    1357
4      10.50       ANU        2014-07-04 07:35    1358
5      10.50       ANU        2014-07-04 07:45    1359
6      09.50       APL        2014-07-04 07:55    1360
7      10.50       APL        2014-07-04 08:15    1361
8      10.50       APL        2014-07-04 08:25    1362
9      13.50       ANU        2014-07-04 08:35    1363
10     10.50       ANU        2014-07-04 08:45    1364
11     10.50       APL        2014-07-04 09:05    1365
12     14.50       APL        2014-07-04 09:15    1366
13     10.50       ANU        2014-07-04 09:16    1367
14     19.50       APL        2014-07-04 09:37    1368
15     10.50       ANU        2014-07-04 10:45    1369

This is a sample information for one day. In my report I have to show all the sales but as ranges, per day. I don't have problem with getting the information for each day. The problem is that, in this example, for today July 7th, I have 15 sales and it has to show like this:

TOTAL       INITIAL CORRELATIVE        ENDING CORRELATIVE     STATUS

23.50            1355                     1357                 APL
21.00            1358                     1359                 ANU
30.50            1360                     1362                 APL
23.50            1363                     1364                 ANU
25.00            1365                     1366                 APL
10.50            1367                     1367                 ANU
19.50            1368                     1368                 APL
10.50            1369                     1369                 ANU

As you can see it groups the first three sales as a range based on correlatives, but when we have one or more void invoices, it cuts the range and inserts a range of void invoices (1358 to 1359) then continues to the next range.

[UPDATE]: After Erwin's right answer I leave the query that we coded according to his:

SELECT sum(total)      AS total
  ,min(num_documento) AS initial_correlative
  ,max(num_documento) AS ending_correlative
  ,estado,grp
  ,count(1) AS ct
FROM  (
   SELECT *, count(step OR NULL) OVER (ORDER BY vtaprsv_id) AS grp
   FROM  (
      SELECT *, lag(estado) OVER (ORDER BY vtaprsv_id) IS DISTINCT FROM estado AS step
      FROM  (
         SELECT total, num_documento, vtaprsv_id, fecha_venta
              , CASE estado WHEN 'PEN' THEN 'PGD' WHEN 'APL' THEN 'PGD' ELSE estado END estado
         FROM   venta_variada
         WHERE  fecha_venta BETWEEN '2014-07-02 00:00:00' AND '2014-07-02 23:59:59'
         ) consi
      ) x
   ORDER BY fecha_venta
   ) y
GROUP  BY grp, estado
ORDER  BY grp;

Hope it helps somebody else.

A textbook case for window functions.
Building on a few assumptions, it could look like this:

SELECT grp
      ,count(*) AS ct
      ,sum(total) AS total
      ,min(invoice_number) AS initial_correlative
      ,max(invoice_number) AS ending_correlative
      ,status
FROM  (
   SELECT *, count(step OR NULL) OVER (ORDER BY id) AS grp
   FROM  (
      SELECT *, lag(status) OVER (ORDER BY id) IS DISTINCT FROM status AS step
      FROM   tbl
      ) x
   ORDER BY datetime
   ) y
GROUP  BY grp, status
ORDER  BY grp;

Subquery x marks each beginning of a new group (step).
Subquery y counts beginnings to create a unique number for each group (grp).
Then you can GROUP BY this number in outer query. status is the same for each group per definition.

SQL Fiddle.

Related answers:
 - Select longest continuous sequence
 - On SO: Group By and Aggregate Sequential Numeric Values


How to show the Join with Crows Foot Notation I could be barking up the wrong tree, but I'm trying to illustrate not only what the relationship is between entities, example, Customers shop in many stores, but also how to join the two entities. For example, two tables:

Customer Data

CustomerID | Name
11112      | Monkeyhead
11113      | Monkeybutt

Customer to Store data

CustomerID | StoreID | Lifetime Purchase
11112      | ABC     | $23
11112      | DEF     | $45

I know that it's a many to one relationship between the two entities, Customer Entity and Customer to Store Entity; but how do I illustrate that it's joined via the CustomerID columns?

Is crows foot notation the right one to use? If not, which is?

There ought to be another entity, whose key is StoreID.
There is a many to many relationship between stores and customers.

In a simple ERD, you can just draw a line between the two boxes for Store and Customer and put a crow's foot at either end.  You can't put lifetime purchases in either of those two boxes.  So if you want to include that attribute in your diagram, add a diamond in the middle of the line you just drew.

The diamond diagrams the relationship, and you can put lifetime purchases in there.

When you go to design a relational database, all of the attributes will end up being stored in relational tables.


How can I solve this error InnoDB: is in the future? InnoDB: is in the future! Current system log sequence number 412018685584.
InnoDB: Your database may be corrupt or you may have copied the InnoDB
InnoDB: tablespace but not the InnoDB log files. See
InnoDB: http://dev.mysql.com/doc/refman/5.6/en/forcing-innodb-recovery.html
InnoDB: for more information.
2014-06-22 00:53:17 7fe0b414a780  InnoDB: Assertion failure in thread 140603070654336 in file fsp0fsp.cc line 3377
InnoDB: Failing assertion: xdes_mtr_get_bit(descr, XDES_FREE_BIT, header_page % FSP_EXTENT_SIZE, mtr) == FALSE

What is the best way to solve this? Clearly I have ibdata1 and my tables, so the data is there? Why can't it just fix this and load past it? Innodb_recovery doesn't work.

Also don't tell me to use backups, this is a 200gb+ table and that would take way too long! This was caused by a mariadb upgrade from maraidb 5.5 to mariadb 10.0.

InnoDB doesn't provide means to fix corrupt tablespace. innodb_force_recovery allows you to disable some checks so you can start MySQL and dump your data. Still you have to re-create the table space and load it back.

If innondb_force_recovery doesn't work then the only way I'm aware of is to fetch records directly from InnoDB files... and re-create InnoDB tablespace from scratch.


Creating a MySQL db from an Informix database There is an existing database that was created in Informix. I would like to export it and then import it into MySQL.

I tried to export it using dbschema and it created the dbase.sql file and several table00123.unl files for the tables.

I tried to import the .sql file into MySQL and it didn't like the syntax of the files that Informix exported.

I just wanted to know the best way to export/dump/output an Informix database that can be imported into MySQL.

You first need to convert that output file nearest to MySQL syntax and convert the data type according to MySQL. You could user perl or sed scripts for first time and then use these techniques for future use. Once you have prepared the files of table creation then generate dynamic insert statements from informix and use them in MySQL for loading data.

Regards,
Nawaz


Joining 2 tables- in Select statement show how many fields matched directly (A1 = B1), vs matched on isnull(A1,B1) = B1 I have 2 tables which I need to join,
but sometimes there are

1) direct joins e.g. t1.FieldA = t2.FieldA

OR

2) when t2.FieldA is null then it must join as well then t2.FieldA is null t1.FieldA = isnull(t2.FieldA,t1.fieldA)

BUT

I have to keep track of how many were direct joins, how many were isnull joins, since there are precedent levels which need to be calculated, e.g. even though the items
joined the one where there are 5 direct joins are higher than when it joined on 5 isnull values

Now it is a hell of a mission to have the following in the Select statement, since there can be more than 5 fields to compare.

e.g. I need to test

Does all 5 match?
Does A, B, C, D  match? then 4
Does A, B, D, E  match? then 4
Does A, B, C, E  match? then 4

etc.

This is way to sloppy, I am looking for help on method to count how many were directly joined please

case when t1.A = t2.A and t1.B = t2.B and t1.C = t2.C and t1.D = t2.D  and t1.E = t2.E  then  5
     when t1.A = t2.A and t1.B = t2.B and t1.C = t2.C and t1.D = t2.D                 then  4
     when t1.B = t2.B and t1.C = t2.C and t1.D = t2.D and t1.E = t2.E                 then  4
     when t1.A = t2.A and t1.E = t2.E                 then 2

Here is a SQL Fiddle

How about :

CASE WHEN t2.A IS NOT NULL THEN 1 ELSE 0 END
+ CASE WHEN t2.B IS NOT NULL THEN 1 ELSE 0 END
+ CASE WHEN t2.C IS NOT NULL THEN 1 ELSE 0 END
+ CASE WHEN t2.D IS NOT NULL THEN 1 ELSE 0 END
+ CASE WHEN t2.E IS NOT NULL THEN 1 ELSE 0 END


how to generate the root CA.pem in mongodb for configuring the ssl certificates? I am trying to configure the SSL certificates in the MongoDB community edition.
The configuration of my mongod.conf
net:
  port: 27017
  bindIp: 0.0.0.0  # Enter 0.0.0.0,:: to bind to all IPv4 and IPv6
 addresses or, alternatively, use the net.bindIpAll setting.
  ssl:
    mode: requireSSL
    PEMKeyFile: /etc/ssl/mongodb.pem
    CAFile: /etc/ssl/ca_bundle.crt
    allowConnectionsWithoutCertificates: false
    allowInvalidHostnames: false
    disabledProtocols: TLS1_0,TLS1_1

Here what I am doing is converting certificate.crt and private.key as mongodb.pem and passing the ca_bundle.crt as CAFile in mongod.conf
I am trying to connect with the server using the command
mongo --ssl --sslPEMKeyFile /etc/ssl/mongodb.pem --sslCAFile /etc/ssl/ca_bundle.crt --host myapptest.tk

Getting the error like

MongoDB shell version v4.0.8
connecting to: mongodb://myapptest.tk:27017/?gssapiServiceName=mongodb
2019-04-04T19:57:40.401+0000 E NETWORK [js] SSL peer certificate validation failed: unable to get local issuer certificate
2019-04-04T19:57:40.402+0000 E QUERY [js] Error: couldn't connect to server myapptest.tk:27017, connection attempt failed: SSLHandshakeFailed: SSL peer certificate validation failed: unable to get local issuer certificate :
connect@src/mongo/shell/mongo.js:343:13
@(connect):2:6
exception: connect failed

I want to know, how to create the root_CA.pem file and pass it as --sslCAFile?
where can found the ca.pem file in MongoDB?
Could anybody suggest me how to configure the SSL certificates in MongoDB?

how to generate the root CA.pem in mongodb for configuring the ssl
  certificates?

As per MongoDB documentation here The procedure creates both the CA PEM file and an intermediate authority certificate and key files to sign server/client test certificates.

  TESTING PURPOSES ONLY:  This will provides some guidelines for creating test x.509 certificates:

  Do not use these certificates for production. Instead, follow your    security policies.
  For information on OpenSSL, refer to the official OpenSSL docs.    Although this tutorial uses OpenSSL, the material should not be taken
  as an authoritative reference on OpenSSL.

for further your ref here


Exactly same query and plan but different duration and total reads. I Know parameter sniffing but I know what is parameter sniffing but this seems different, I have this simple query:

select * from A400RDATA

This table has only a cluster index and approximately 25000 rows. The query usually takes 1 second but now It took 20 seconds.

I found its execution plan in the cache and I used sp_BlitzCache (thanks Brent Ozar) for analyze every details -> It's true, the last execution takes 20 seconds.

So I run this query again with a little change, a leading space: "select * from A400RDATA" (note leading space before s of select). The leading space is a change in the query text so SQL Optimizer generates a new query plan.

The two query plans are identical, they are trivial plans and they just use the cluster index, but the total reads and duration are different.
Following the two different BlitzCache results:

Solarwind shows a drop in Page Life Expectancy, and SQL Server instance uses only one core (MAXDOP = 1).
I think MAXDOP and PLE can be the cause of the issue, what do you think ?
How can I be sure of that?
How can I know if the data was taken from disk or RAM?

I checked the logical and physical reads of the 20 seconds execution plan:

Execution time: 3
Physical Reads: 0
Rows Processed: 76000
Logical Writes: 0
Logical Reads: 3841

No physical reads.......
So PLE isn't the cause (perhaps).
I have read again the plan's details: 3 executions and 20 seconds of total duration, so each execution took 6 seconds but relatively little CPU time:

Total Duration (ms) = 20,013.84
Total CPU (ms) = 192.57

Can be CPU the problem? Maybe only one CPU is not enough and SQL brings the query in RUNNABLE state (waiting for CPU time).

The two query plans are identical, they are trivial plans and they
  just use the cluster index, but the total reads and duration are
  different. Following the two different BlitzCache results:

Total reads in that result set are most likely totalled across multiple executions. See the total rows column is 76,218 but the min, max and avg are identical to the other execution? This suggests the original plan had been executed 3 times so don't look at the total, look at the avg, which is identical.

You need to look at whether these reads are logical or physical because the variance in execution time suggests they're possibly logical and the physical read differences may shed more light on your situation.

  Solarwind shows a drop in Page Life Expectancy, and Sql server
  instance uses only one core (MAXDOP = 1). I think MAXDOP and PLE can
  be the cause of the issue, what do you think ? How can I be sure of
  that ? how can I know if the data was taken from disk or ram ?

If you only have a single core then both query executions will use MAXDOP = 1, so that is unlikely to cause any variation. PLE could be a symptom of a deeper issue causing your performance issue, but it's not the root cause. If you're seeing PLE consistently low or has an extreme sawtooth pattern, you may find that determining the root cause of that helps identify this query's problem.

What you're describing doesn't sound like parameter sniffing because your query has no WHERE clause. It sounds like the query is having to read pages from disk on the first execution (physical read) into the buffer cache and subsequent reads are occurring in memory (logical read), which is typically much faster than physical reads.

Take a look at this article for a description of physical vs logical reads. Basically, when you first executed it took 20 seconds and this is caused by reading pages from disk. The next execution wasn't faster due to the trivial change or the new plan (as you stated, the actual plans were identical) but because those data pages were read from memory instead of disk. If you were to flush the buffer cache and re-run the queries again you would likely see the same behaviour - 1st execution = 20 secs, 2nd execution = 1 sec.

You can force this behaviour to occur to see it in real-time. On a non-production server, run the following:

DBCC DROPCLEANBUFFERS

SET STATISTICS IO ON

SELECT * FROM A400RDATA

SET STATISTICS IO OFF

Review the output in the messages pane and note down the Physical Reads and Logical Reads values. Run the below lines again:

SET STATISTICS IO ON

SELECT * FROM A400RDATA

SET STATISTICS IO OFF

Check the output and compare the physical reads and logical reads values again. Physical reads should have dropped significantly, most likely down to 0, and logical reads will be the same or higher, indicating pages are now read from memory without needing to fall back to disk reads. Execution time should be quicker as well.

How do we confirm this in production for queries that have already executed? Well it will be hard to be definitive without having monitoring and metrics collection in place prior, however, if you query sys.dm_exec_query_stats using the plan handle you can get stats on recent executions. Check the last, min and max physical reads. If min is 0 and max is some higher number this indicates some executions are reading from disk. If query execution has returned to 1 sec, last should be 0 or some really low number.

Also check the last, min and max elapsed time columns from that DMF as well to see if there is a variance in executions times for the 20-second execution plan, I suspect there will be. Solarwinds may help you correlate a drop in PLE with the increase in execution times for this query, since a drop in PLE could indicate these data pages aged out/were forced out of the cache and caused the physical reads to increase. Check out this article for useful hints on optimising/managing PLE to maintain your servers performance.


Count records with same id but different column value I have the following table:

+------+------+
|userID|Name  |
+------+------+
|1     |sport |
+------+------+
|2     |it    |
+------+------+
|3     |game  |
+------+------+
|1     |sport |
+------+------+
|1     |game  |
+------+------+
|3     |it    |
+------+------+
|3     |sport |
+------+------+
|2     |it    |
+------+------+

I want to count the total number of records per UserID and if all of those are unique/have the same Name.
Example:

userID 1: sport, sport, game
userID 2: it, it
userID 3: game, it, sport

I should get:

userId 1: 3, False
userID 2: 2, True
userID 3: 3, False

The total number of rows per UserID is easy, you just need to use COUNT(*). As for the other column, then, assuming Name cannot be null, you need to count distinct Name values and compare the result to 1. To explain: if all names are identical, COUNT(DISTINCT Name)) will return 1, otherwise it will return a different number. Thus, by comparing the result to 1 you will determine whether all names are unique or not.

This is how I would implement it in SQL:

SELECT
  UserID,
  COUNT(*)                   AS TotalRows,
  (COUNT(DISTINCT Name) = 1) AS AllNamesUnique
FROM
  YourTable
GROUP BY
  UserID
;

Feel free to play with this solution at dbfiddle.uk.


Optimising MySQL count() with INNER JOIN (550k rows) I have the query below, which is currently taking approx 1 - 1.5 seconds to run in a MySQL 5.7 database. It's being called from a web application, and whilst I'll be implementing caching at the application (PHP) level, I'd still like to try and speed up this query if possible. There are approx 550k rows in the table.
SELECT
    count(*) AS aggregate
FROM
    `establishments`
    INNER JOIN `inspections` ON `inspections`.`id` = `establishments`.`latest_inspection_id`
    INNER JOIN `ratings` ON `ratings`.`id` = `inspections`.`rating_id`
WHERE
    `establishments`.`latest_inspection_id` IS NOT NULL
    AND `ratings`.`key` in('best', 'average', 'poor', 'terrible', 'exempt')
    AND `establishments`.`deleted_at` IS NULL

This is the result of EXPLAIN on the query:
| id | select_type | table          | partitions | type   | possible_keys                               | key     | key_len | ref                                               | rows   | filtered | Extra       |
|----|-------------|----------------|------------|--------|---------------------------------------------|---------|---------|---------------------------------------------------|--------|----------|-------------|
|  1 | SIMPLE      | establishments |            | ALL    | establishments_latest_inspection_id_foreign |         |         |                                                   | 526482 |     5.00 | Using where |
|  1 | SIMPLE      | inspections    |            | eq_ref | PRIMARY,inspections_rating_id_foreign       | PRIMARY | 8       | fhrs_insights.establishments.latest_inspection_id |      1 |   100.00 |             |
|  1 | SIMPLE      | ratings        |            | eq_ref | PRIMARY,ratings_key_unique                  | PRIMARY | 8       | fhrs_insights.inspections.rating_id               |      1 |    33.33 | Using where |

Any advice on how I can speed this up?

First I would try index on establishments (latest_inspection_id, deleted_at).
Also it may has a sense to eliminate join with ratings table using condition directly on inspections.rating_id instead. I mean AND inspections.rating_id IN (..list of corresponding ids..)


Management Studio System.OutOfMemoryException I'm using Microsoft SQL Server 2012 and trying to run a simple query against it within Management Studio. I'm getting the following error (in SSMS, running on the server):

  An error occurred while executing batch. Error message is: Exception
  of type 'System.OutOfMemoryException' was thrown.

The system has 24GB of RAM installed but looking in task manager the sqlservr.exe process is only using 2.9GB.

Is there a setting somewhere that is restricting its RAM usage?

This error indicates that Management Studio is running out of memory, not the SQL Server service. Even if you installed 64 bit SQL Server, the SQL Server Management Studio executable is a 32 bit application.

This is likely caused by the size of the result set that you are returning to Management Studio. Are you executing something like SELECT * FROM really_big_table? See http://support.microsoft.com/kb/2874903 for more.


A database of all combinations of 39 numbers taken 5 at a time, total combinations = 575757 I want to create a numerical database of all combinations of 39 integers(1 Through 39) taken 5 at a time. I know the total possible combinations = 575757. This would be the size of the database.

I would then like to query the database for various criteria, e.g. number of combinations that are made up of all even numbers, all odd numbers, 1 even, 1 odd, 2 even, 2 odd, etc,  and then query those results for other criteria such as restricted sums of all integers, and range distribution.

How would I do this? Obviously this is a Lottery oriented idea and I expect nothing but ridicule and disdain.

Ridicule and disdain aside, the following query would return all possible permutations of your integers.

SELECT n1.i, n2.i, n3.i, n4.i, n5.i
FROM numbers AS n1
INNER JOIN numbers AS n2 ON n1.i<n2.i
INNER JOIN numbers AS n3 ON n2.i<n3.i
INNER JOIN numbers AS n4 ON n3.i<n4.i
INNER JOIN numbers AS n5 ON n4.i<n5.i

.. given that the table or common table expression numbers contains 39 unique records with 1<=i<=39.

You could add different filters for your purposes, like how many odd numbers:

WHERE (CASE WHEN n1.i%2=1 THEN 1 ELSE 0 END)+
      (CASE WHEN n2.i%2=1 THEN 1 ELSE 0 END)+
      (CASE WHEN n3.i%2=1 THEN 1 ELSE 0 END)+
      (CASE WHEN n4.i%2=1 THEN 1 ELSE 0 END)+
      (CASE WHEN n5.i%2=1 THEN 1 ELSE 0 END)>howmanyoddnums

.. or even numbers (WHEN nx.i%2=0 THEN ...). And because the numbers are "ordered" with the < in the join, n1.i will always be the lowest and n5.i the highest.

Any more advanced features (or tuning for that matter) will come down to what platform and dialect of SQL you're using.


Can I connect to the database as the user "dbo"? If not, why?

During security audit we are being asked if the dbo login is interactive. My understanding is that the account is automatically created and managed by SQL server itself and is not directly interactive.

I have tried to the demonstrate that a sysadmin users would appear in SESSION_USER as dbo when authenticating but they're not fully convinced. Their question is now if it is possible to directly authenticate to dbo by logging in with its password.

Is this possible? I don't even know where I would find dbo's password if it has one.

The first part to understand is the difference between a User and a Login in SQL Server. This article provides a brief rundown to understand the difference, but to put it simply, a Login is a server-level principal that provides authentication and authorisation for server-level access. A User is a database-level principal that provides authorisation for database-level access.

There are many scenarios, but typically a Login is mapped to one or more Users, but only one User per database. A User is only ever mapped to one Login (except for contained database users, but that is a different scenario that can be ignored for now).

The dbo User is a built-in database user in every single database that represents the Database Owner. This user has full unrestricted access to the database. While you cannot login to a server as "dbo", you can login with a login that is mapped to the dbo user in one or more databases. When the database context of the connection is a database where that login is mapped to dbo, then the identity in that context is as "dbo".

In addition, as per comments, any login that has the sysadmin role will appear as dbo in every database as well, even if they're not explicitly set as the database owner.

To answer your specific questions:

  During security audit we are being asked if the dbo login is
  interactive. My understanding is that the account is automatically
  created and managed by SQL server itself and is not directly
  interactive.

Yes, the dbo account is built-in and cannot be deleted, locked, disabled or removed. It also cannot be logged into directly, i.e. you cannot login as user "dbo" with a password.

  I have tried to the demonstrate that a sysadmin users would appear in
  SESSION_USER as dbo when authenticating but they're not fully
  convinced. Their question is now if it is possible to directly
  authenticate to dbo by logging in with its password.

  Is this possible? I don't even know where I would find dbo's password if it has one.

No, it is not possible. There is no "password" that exists for dbo. There may be a password for the mapped Login if it is a SQL Authentication login, but no direct login for dbo.

This article provides an explanation of dbo and also some useful queries for identifying your logins that are mapped to dbo in each DB. I would suggest you advise the auditors that it is not possible in SQL Server to login directly as dbo, nor can you adjust any permissions or disable/remove this account, then provide them the list of mappings and a list of sysadmins or logins with CONTROL SERVER permissions to illustrate who can assume the dbo user context in each database.


Fill in a table name using a variable from another query I have a general query I need to run for many tables

SELECT sum(honoraria_amount)
FROM table_1

The table IDs I have a list, from another query. For example, I want to run this for: table_1, table_39, table_42, etc.

Since I have a query that returns the IDs:

table_id
----
1
39
42

Is there anyway I can feed these query results, into the sum query, like some form of replacing with a variable or array to iterate through?

If its possible I'd like to replace x below with the IDs and maybe loop through all the queries to print out all the sums:

SELECT sum(honoraria_amount)
FROM table_x

Normally I'd advise against dynamic SQL as it can't be cached by the engine, but I don't see any other alternative that doesn't add a hurdle every time you wish to add a new one of these table_x's.

With that in mind, the following function will help you out:

create or replace function sum_table(
    tid integer
) returns numeric as $$
declare
    _out numeric;
begin
    execute 'select sum(honoraria_amount) from table_' || tid::text || ';' into _out;
    return _out;
end;
$$ language plpgsql;

And then to use it in a query:

with
    __tables as(
        select unnest(array[1, 39, 42]) as table_id
    )
select
    table_id,
    sum_table(table_id)
from
    __tables


MongoDB Configuration I am new to MongoDb, I have 1 TB of Data in JSON format, what should be the processor speed, RAM, disc space required, network bandwidth for maximum of 10 concurrent users for production environment.

I can just give you rules of thumb, of course. But it may help you.

Disk space and setup

In order to be able to create backups in a proper way, you should have at least have a RAID10 (the suggested RAID level for MongoDB) with a payload of around 2.5TB. Let me explain why:

You don't want to have 100% utilization of your disk space, so we add a reasonable security margin of 20% to give you enough time to take measures.

( 1024GB payload * 100 / 80 ) = 1280GB with security margin

Then, you want to be able to create backups using LVM snapshots. You need to allocate some space for changes which occur during the lifetime of the snapshot. This is hard to calculate without information, but to be on the safe side, we assume it is about 50% during the time of the backup, so we add another 500GB. Then, you would need some space to compress the backup to. To be on the safe side again, we add another 500GB. Since you don't want a utilization of 100% of the backup space, too, the above calculation for the space needed for backup applies again! effectively doubling the space.

The closest would be a RAID 10 with 6 disk of 1TB each giving you a payload of 3TB. That should give you the best balance between redundancy, performance and required payload. Please use 15k SAS disks at least. SSDs give an performance boost of about an an order of magnitude right out of the box, but that does not seem to be an issue with your requirements.

CPU

Hard to say what computing power you need, but as a rule of thumb you should buy fewer, but more powerful cores.

RAM

That is very hard to say without knowing how many and which indices you have. But again a rule of thumb jumps in: With MongoDB, there is no such thing as too much RAM. With 1TB payload, MongoDB could utilize 1TB+ to keep not only the indices, but the data, too in in RAM for fast reads.

The amount of RAM you need is hard to calculate. I usually would roughly calculate 10% to be on the safe side for optimal performance, since additional RAM never hurts. Especially when using spinning disks instead of SSDs.

That would be 128GB in your case. But it may well be that you get along with just 64 or even 32GB. My suggestion would be to start with 32GB and upgrade if you see a lot of page faults in MMS.

Network bandwidth

Without knowing how much data is send per query, it is impossible to calculate this. If you plan to set up a replica set (which you almost always should), you should not go below 1Gb internal bandwidth on write heavy applications. If you really have a lot of load, you might even want to bond two interfaces.

Disclaimer: These are only rules of thumb, based on the very limited information of the original question. Depending on your application, system administration skills and environment, your actual needs may vary.


LEFT vs CHARINDEX for matching the start of a string in T-SQL I want to match the beginning of a string in a WHERE clause. While I recognise that so often database-performance is dependant on the underlying data structure, is there a best-practice for doing this? Is there one option which always outperforms the rest?

I want to avoid LIKE as I assume it will be less efficient than LEFT or CHARINDEX. As far as I can tell, my choices are below. Other suggestions welcome:

DECLARE @MyField as varchar(10)
SET @MyField = 'HELLOWORLD'
SELECT 1 WHERE @MyField LIKE 'HELLO%'
SELECT 2 WHERE LEFT(@MyField, 5) = 'HELLO'
SELECT 3 WHERE CHARINDEX('HELLO', @MyField) = 1

Kind Regards,

Jase.

The biggest factor that I can think of is that LEFT(MyField,5) and CHARINDEX('HELLO',@MyField) won't use an index unless the index matches the expression exactly.

However MyField LIKE 'HELLO%' will use any index with MyField in it.


Replicating a Table Structure With no Data Using Merge Replication I am using merge replication with SQL 2012.

I have a table, and it would be useful to propogate that table to the subscribers, but just the table structure, not the actual data.

The data in the table is populated per session, i.e. when you log into the software data is added into the table, and then deleted when you exit the software.

What this means is that the data in the table shouldn't be replicated from publication to subscriber, or from subscriber to publication. But it would be good for the table structure to be replicated so that we don't have to run a script manually at the subscribers.

You can use the command sp_addscriptexec to run an ad-hoc script on all the subscribers.


SSIS deployment on databases part of AAG Experts, I have two databases, one is SSISDB, other is user database. Both of them are a part of Always on availability group. I need to give one user a permission to be able to deploy a package. How should I go about doing this. He already has a login and has a data reader access to both the databases. We are talking about SQL server 2016 EE here.
Thanks.

You have 2 options.

Make the user a member of ssis_admin role which of course give more privilege than you intend to.

  This role provides full administrative access to the SSIS Catalog
  database.

You can give permission at folder level under integration services catalogs.  Here you have a better control, but you have to do this for each folder.
Details explained here: SSIS Catalog


What will happen if the primary shard for a mongodb database is down? Well as I understand each database has it's primary shard, which is mostly the first shard added to mongodb cluster. And we know that balancer will not split chunks until the chunksize is bigger than the half of the max chunk size, so the balancer will not move any data to the specified shards, for example when we use tag aware sharding. So I wonder what will happen when the primary shard is down?

Thanks a lot.

I guess you mean primary shard is down, because the term primary database is not applicable. The behavior will be the same as losing any other shard. The difference is that both sharded and unsharded collections will get affected.


How to efficently handle large amounts of insert statements I have a file containing a large amount of insert statements roughly 30 GB worth of them. The insert statements themselves are rather simple and contain 5 different values. I now have the requirement the insert the data from these statements into a SQL database on Azure. Which tool would be best to easily and efficiently insert the data? This is a one-time thing, so the solution does not need to be elegant or repeatable. The key priority is getting it done with as little time spent on the solution as possible. Thanks for reading this.

If each line of the file is a well-formed SQL INSERT statement, listing target columns and has a VALUES clause you can simply submit is as input using the command line tool of your choice. It will be slow but it will work. It would be better to split the one 30GB file into many smaller files (perhaps about 100MB each?) and have a script submit each in succession. Then failures can be more easily recovered, the server will become less flooded, and the load can be halted and resumed as required.

If the file can be converted to raw data, say a CSV format, that can be submitted using whichever ETL or bulk load tool you have. Again, I'd split the source into multiple smaller files for easier management.

Of course getting the source files as close as possible to the server will reduce run-time network latency.

Finally, disable all triggers, foreign keys, secondary indexes etc. on the target table. They can be rebuilt or rechecked after all the data is in place. It will be faster overall than having them checked for each row as it is processed. Make sure the DB has sufficient disk to handle the new data and the DBMS can get to it. Figure out how you'll manage log growth as data is processed. Halt all other activity on the server while the load is in progress.


SUM and Group By Optimization I use this query:

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

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

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

with this keys:

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

and this execution plan:

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

InnoDB Config is:

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

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

The questions:

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

Would i benefit in this case from the myisam table engine?

No you won't.

  What could i do to further optimize the query or the settings?

Materialize the query.

MySQL does not have built-in means to do that easily (similar to indexed views in SQL Server or materialized views in Oracle) so you'll have to put some effort into it.

Create a table like this:

CREATE TABLE log_customer_date
        (
        customer BIGINT NOT NULL,
        logdate DATE NOT NULL,
        sumbytes BIGINT NOT NULL,
        countrecords BIGINT NOT NULL,
        PRIMARY KEY (customer, logdate)
        )

fill it:

INSERT
INTO    log_customer_date
SELECT  customer, logdate, SUM(bytes) sumbytes, COUNT(*) countrecords
FROM    logdata
GROUP BY
        customer, logdate

and add further records in a trigger:

INSERT
INTO    log_customer_date
VALUES  (NEW.customer, NEW.logdate, NEW.bytes, 1)
ON DUPLICATE KEY
UPDATE  sumbytes = sumbytes + VALUES(sumbytes),
        countrecords = countrecords + VALUES(countrecords)

or in a script every once in a while:

INSERT
INTO    log_customer_date
SELECT  customer, logdate, SUM(bytes) sumbytes, COUNT(*) countrecords
FROM    logdata
WHERE   id > :lastid
GROUP BY
        customer, logdate
ON DUPLICATE KEY
UPDATE  sumbytes = sumbytes + VALUES(sumbytes),
        countrecords = countrecords + VALUES(countrecords)

, recording the highest id by the moment of the insert somewhere.


How to create Oracle RAC database using dbca silent mode with different db_name and db_unique_name I have a requirement to use dbca (version 11.2.0.3) silent mode to create a RAC database on a 2-node RAC and the db_name and db_unique_name must be different, I tried following dbca command

$ORACLE_HOME/bin/dbca -silent -createDatabase -templateName 'General_Purpose.dbc' -adminManaged -emConfiguration NONE -gdbName 'oiddb' -sid 'oiddb' -sysPassword oracle -systemPassword oracle -asmSYSPassword oracle -asmsnmpPassword oracle -storageType ASM -diskGroupName DATA -datafileJarLocation /u01/app/oracle/product/11.2.0/dbhome_1/assistants/dbca/templates -nodelist 'node1,node2' -characterset AL32UTF8 -obfuscatedPasswords false -sampleSchema false -oratabLocation /etc/oratab -initParams db_unique_name=oiddbp,processes=7000,db_name=oiddb -listeners LISTENER_OID

But it failed at this error

DBCA_PROGRESS : 85%
ORA-17502: ksfdcre:4 Failed to create file +DATA/oiddb/spfileoiddb.ora
ORA-15173: entry 'oiddb' does not exist in directory '/'

PRCR-1079 : Failed to start resource ora.oiddbp.db
CRS-5017: The resource action "ora.oiddbp.db start" encountered the following error:
ORA-01078: failure in processing system parameters
ORA-01565: error in identifying file '+DATA/oiddb/spfileoiddb.ora'
ORA-17503: ksfdopn:2 Failed to open file +DATA/oiddb/spfileoiddb.ora
ORA-15056: additional error message
ORA-17503: ksfdopn:2 Failed to open file +DATA/oiddb/spfileoiddb.ora
ORA-15173: entry 'oiddb' does not exist in directory '/'
ORA-06512: at line 4
. For details refer to "(:CLSN00107:)" in "/u01/app/11.2.0/grid/log/node1/agent/crsd/oraagent_oracle/oraagent_oracle.log".

I was monitoring my ASM during the run and I can see +DATA/oiddbp got created, but it seems that dbca was trying to create the spfile under +DATA/oiddb.

I then tried to create the database with the same parameters using dbca UI mode and it did create +DATA/oiddbp and spfile under it.

I am wondering if this could be an Oracle bug? Any workaround or how to resolve this? Please help, thanks.

Since you asked RAC, please see if you can use this non-RAC example as a starting point.
For db_name != db_unique_name != sid
- Non-RAC
- Oracle 12.1
- Custom Database (not seed)

On the command line, set ddbname == parameter db_unique_name, and also set parameter db_name

dbca    \
-silent \
-createDatabase \
-templateName    wailua.dbt     \
-sid wailua20           \
-gdbname wailuaq        \
-initParams db_unique_name=wailuaq,db_name=wailua       \

where wailua.dbt is copied from New_Database.dbt

db_name and db_unique_name are not mentioned in wailua.dbt

Note that variables
SID and DB_UNIQUE_NAME expand in the template.

[oracle@waipio1 wailua20 templates]$ echo $ORACLE_SID
wailua20

[oracle@waipio1 wailua20 templates]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Wed Jun 11 00:48:22 2014

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options

SQL> show parameter db_name

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_name                              string      wailua
SQL> show parameter db_unique_name

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_unique_name                       string      wailuaq

oratab:
wailua20:/app/oracle/product/12.1.0/db_1:N:         # line added by Agent

ASM:
asmcmd ls data/WAILUAQ/
CONTROLFILE/
DATAFILE/
ONLINELOG/
PARAMETERFILE/
TEMPFILE/
spfilewailua20.ora

Full crdb.wailua.ksh dbca script: http://pastebin.com/vx3k5g3U/

dbca log: http://pastebin.com/8UQDEDZn/


Application == session == connection in DB2? Can I consider as synonyms Applications, Sessions and Connections in DB2 LUW?

In db2top, I see "Sessions". There is a command "db2 force applications", views and other stuff. And from the developer point of view it is a "connection"

May I use them interchangeably when talking about connections?

For the most part, application == connection == session when looking at it from DB2's perspective.

Someone could probably make an argument that these aren't always necessarily the same in environments using connection concentrator or the database partitioning feature (DPF), but for all intents and purposes it's safe to use the terms interchangeably.

You could also make the argument that, from the client-side, a single application may open multiple connections (i.e. a connection pool), but this is dba.stackexchange.com :-)


Does ALTER on PostgreSQL, Oracle table (respectively) create intermediate copy of the table? I've heard MySQL admin claim that ALTER on MySQL table creates an intermediate copy of the table during the operation (don't know if the type of table involved were supposed to be MyISAM or InnoDB), which is a problem when working on huge tables.

Does this apply to ALTER on PG db tables and Oracle db tables, respectively?

What are high-level mechanics of ALTER (I'm esp. interested in PG) that can impact db for huge tables (one legacy Postgres DB I inherited has a table that weights nearly 400GB.., entire DB is ~1TB)?

From the Postgres manual:

Adding a column:

  When a column is added with ADD COLUMN, all existing rows in the table are initialized with the column's default value (NULL if no DEFAULT clause is specified). If there is no DEFAULT clause, this is merely a metadata change and does not require any immediate update of the table's data; the added NULL values are supplied on readout, instead.

(Emphasis mine)

Changing a column's type:

  Adding a column with a DEFAULT clause or changing the type of an existing column will require the entire table and its indexes to be rewritten. As an exception when changing the type of an existing column, if the USING clause does not change the column contents and the old type is either binary coercible to the new type or an unconstrained domain over the new type, a table rewrite is not needed; but any indexes on the affected columns must still be rebuilt.

Note the part about indexes being rebuilt for any column change.

Dropping a column:

  The DROP COLUMN form does not physically remove the column, but simply makes it invisible to SQL operations. Subsequent insert and update operations in the table will store a null value for the column. Thus, dropping a column is quick but it will not immediately reduce the on-disk size of your table, as the space occupied by the dropped column is not reclaimed.


Is a Non-Clustered index needed when referencing a small table with a Foreign Key I'm not a DBA, excuse me if this question sounds dumb. Hope I can get help from an experienced DBA, because I'm questioning this for a long time.
Assume there are 2 tables, one table with a lot of records, and one table is just static data with 2 records (for example: a Yes/No table).
As example take these 2 tables:

Table_Yes_No (only 2 records: Yes and No. Of course they have an incrementing clustered PK)
Table_Form (Very big table with lots of records, and one column has FK to the PK of Table_Yes_No).

Now my question; Is it worth putting an index on the FK (reference from Table_Form to the Table_Yes_No table)? The column will only contain 1 or 2 (but not sorted, because no index). Is it worth indexing such a FK?
An example in SQLFiddle: http://sqlfiddle.com/#!18/51614/3/0
That column will be queried anyway, the question is will the index help the performance or not.
The small table is static data, will never be changed. The big table will be queried heavily, and there will be also a lot of CRUDs on it.

Short answer
No.
Long answer
No, and adding the index could be detrimental to performance.
Something that factors heavily into whether or not a secondary/non-clustered index is even used is how selective it is (and the searches you are trying to perform are).  Y/N has two values - it's ability to be selective is going to depend on the proportion of Y to N.  If they are evenly split, and there is no rhyme/reason to Y/N with relation to the clustered index, they will not be selective and the query optimizer will almost always ignore that index.
The potential slowdown occurs if you issue a delete on the larger table or an update against that Y/N column.  There's no efficient way to update the secondary index as it is organized by Y/N - so the only option is to basically scan the entire index looking for the record to update/delete.  Depending on the number of rows in the main table and the size of the clustered index, this could be an issue.
Caveats
If there are few Ys or few Ns, and you need to locate those records quickly, you can use a filtered index to create a very small index that contains only records for the sparsely populated value.  This can be beneficial and has a much lower footprint than a traditional non-clustered index would in that situation.
If you're just trying to enforce Y/N (or some other easily understood code set), that can be handled easily through a check constraint, which will have a lower overhead than the FK.
ADD CONSTRAINT CK_<ColumnName>_Is_YesNo CHECK (<ColumnName> IN ('Y','N'))


When (or why even) use PLPython(3)u As I gain more experience with PostgreSQL I start to question the existence of PLPython. It's considered an "untrusted" language https://www.postgresql.org/docs/10/plpython.html
What I am wondering is, when or why would anyone need to use this? PLPGSQL is already quite a strong language that allows you to do a lot of things. Has anyone here had the need to use it, and if so, for what?

I find pl/pgsql tedious for general programming, and slow to program in and slow to execute.  And missing a lot of functionality--some of which is missing because it has to be in order to be a trusted language (no IPC or network), and others just because it hasn't been built and it lacks a mechanism for libraries/modules/packages.
I've used plpython3u get access to chemical intelligence libraries from inside the database.  re-implementing the functionality of those libraries from scratch would be theoretically doable, but utterly impractical.  Doing it in pl/pgsql would be utterly impractical squared.
I've done the same thing with plperlu and plperl, to get access to a variety of CPAN Perl modules.  If those modules happened to have been in python rather than Perl, I would have used plpythonu rather than plperl(u).
I've also used plperl (not plpythonu but just because I prefer Perl over python) to implement functions which I wanted to be available both inside the database through SQL, and outside in our procedural code.  I could have implemented them in pl/pgsql, but that would be annoying to do, and also means they would need two implementations in two languages.  Alternatively I could have just implemented them in the database, and connect to the database from standalone Perl when I want to call the functions from there. But that would sometimes mean establishing a database connection that would otherwise be unneeded in that program, and would be an abuse of one of our more limited resources, as databases are much harder to scale than standalone Perl scripts.  It would also introduce latency.


Query with Variable not estimating right I have a query with several joins that is not performing well. After a lot of triage, I decided to take the query apart and look at just the driving table. Below is an example of the query based on just the driving table.

DECLARE @VAR AS INT

SET @VAR = 11652862

SELECT Field1, Field2
FROM tablea
WHERE Field2 = @VAR

When I run the code as it is the actual execution plan comes back with 15k estimated rows and 1m actual rows. Yes, this is just a simple SELECT...FROM TABLE, but remember this is just part of a bigger query, so this estimate difference greatly compounds as we add more joins.

I know the problem is the @VAR because the optimizer does not know the value of the variable when it compiles the query into a plan so its coming back with the avg # of rows for the estimate for the field. If I add OPTION (RECOMPILE) it will force in the value when it compiles and come back with the correct # of rows.

Here is the kicker: This is not custom code that my company can edit. It is part of a third party app that I cannot edit. Updating stats will not help (I looked at the histogram and it is correctly listing the value for my variable). I already have a covering NC index on those two fields. The only option I can think of is to use a forced execution plan on this query. I have never been able to successfully do that, though. Anyone have any other ideas or a good How-To reference on forced execution plans?

Since it was asked in the comments, the create statement for the index is:

CREATE NONCLUSTERED INDEX idx ON tablea (Field2)

I have other ideas than a plan guide and I'll post them here for your consideration, but I cannot recommend actually implementing them. There are at least two different ways of bumping up row estimates from tablea. The first involves manually setting statistics and the second involves redirecting the third party app to use a view that you defined instead of accessing the tablea directly.

Both CREATE STATISTICS and UPDATE STATISTICS have a STATS_STREAM option. Here is what Books Online says about it:

  Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.

It's completely understandable if that turns you off from using it, but what it allows you to do is to transplant the statistics from one object to another. Suppose that you want to increase cardinality estimates from queries that use tablea by 50X. You could take the data from tablea, duplicate it 50 times, gather stats with FULLSCAN, then update the statistics on tablea using the STATS_STREAM value from the other table along with the NORECOMPUTE option. You probably want to change the ROWCOUNT value as well. This should have a pretty large effect on all queries that reference the table. You could run into problems with other queries, with the underlying data changing in the table, with statistics on indexes not getting updated, and so on. This isn't a good option.

Depending on how the third party application connects to SQL Server, you may be able to create a view with a different schema from tablea and to direct that part of the application to use in the view. The view should have all of the same columns as tablea but should have additional code which inflates the number of rows returned. I didn't try that hard to get this to work because it seems very impractical, but this approach was inspired by Adam Machanic's article about forcing parallel plans:

SELECT ta.*
FROM tablea ta
CROSS JOIN (
    SELECT t.t
    FROM
    (
    VALUES
    (2), (1), (1), (1), (1), (1), (1), (1), (1), (1),
    (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
    -- lots more values here ...
    (1), (1), (1), (1), (1), (1), (1), (1), (1), (1)
   ) t(t)
WHERE t % 2 = 0
) t
OPTION (QUERYTRACEON 8690);

I wasn't really satisfied with that query but didn't spend too much time on it. In that form it does a single scan of the table but the query optimizer overestimates the cardinality estimate by 100X. TF 8690 is there to prevent a pointless table spool, but this probably isn't appropriate in a view or in a larger query. If the same application inserts data into this table you'd need to deal with that somehow. This isn't a good option.

To reiterate, I don't think that you should attempt either of these options. It would be much better to talk to the vendor, or if that doesn't work, to suck it up and try again with a plan guide. I don't have any experience with those so I can't be of help.


Using Pt-online schema change on multi master MYSQL We are using two MYSQL servers in ring replication where master is also a slave of the other master. Both have slaves connected to each other.
We want to run pt-online-schema-change to add couple of columns but we are not sure if it may break the replication due to ring replication in place. Any advice would be great.

If you use a dsn table as a recursion method, then pt-online-schema-change can do the job for you. This is documented here:

https://www.percona.com/doc/percona-toolkit/LATEST/pt-online-schema-change.html#cmdoption-pt-online-schema-change-recursion-method

If you use the hosts method, though, pt-osc will detect that SHOW SLAVE STATUS returns more than 1 row, but it considers the topology is using replication channels. You would get this error:

This server returned more than one row for SHOW SLAVE STATUS but "channel"
was not specified on the command line at bin/pt-online-schema-change line 4494.

For setting up topologies to test different circumstances, dbdeployer is a useful tool https://github.com/datacharmer/dbdeployer#replication-topologies.

Disclosure: I work for Percona


What is the logic behind ISNUMERIC for certain special characters? The ISNUMERIC function has some unexpected behavior. The MSDN documentation says:

  ISNUMERIC returns 1 when the input expression evaluates to a valid numeric data type; otherwise it returns 0. Valid numeric data types include the following: int, bigint, smallint, tinyint, decimal, numeric, money, smallmoney, float, real.

And it also has a footnote:

  ISNUMERIC returns 1 for some characters that are not numbers, such as plus (+), minus (-), and valid currency symbols such as the dollar sign ($). For a complete list of currency symbols, see money and smallmoney (Transact-SQL).

Okay, so +, -, and listed currency symbols are expected to be considered numeric. So far so good.

Now for the odd part. First up, some of the currency symbols from linked article are not numeric, including:

Euro-Currency sign, hex 20A0: ₠
Naira sign, hex 20A6: ₦
Rial sign, hex FDFC: ﷼

This is weird, and I can't seem to find out why? Is this version or environment dependent?

However, things get weirder. Here are a few others I can't explain:

/ is not numeric, but \ is (Huh?!)
REPLICATE(N'9', 308) is numeric, but REPLICATE(N'9', 309) is not

The first and most basic question is: what explains the above cases? More importantly though: what is the logic behind ISNUMERIC, so I could explain / predict all cases myself?

Here's a good way to reproduce things:

DECLARE @tbl TABLE(txt NVARCHAR(1000));

INSERT INTO @tbl (txt)
VALUES (N''), (N' '), (N'€'), (N'$'), (N'$$'),
       (NCHAR(8356)), (NCHAR(8352)), (NCHAR(8358)), (NCHAR(65020)),
       (N'+'), (N'-'), (N'/'), (N'\'), (N'_'), (N'e'), (N'1e'), (N'e1'), (N'1e1'),
       (N'1'), (N'-1'), (N'+1'), (N'1+1'), (N'⒈'), (N''), (N'¹'), (N'①'), (N'½'),
       (N''), (REPLICATE(N'9', 307)), (REPLICATE(N'9', 308)), (REPLICATE(N'9', 309)),
       (REPLICATE(N'9', 310));

SELECT  UNICODE(LEFT(txt, 1)) AS FirstCharAsInt,
        LEN(txt) AS TxtLength,
        txt AS Txt,
        ISNUMERIC(txt) AS [ISNUMERIC]
FROM    @tbl;

When I run this on my local Sql Server 2012 box I get the following results:

FirstCharAsInt   TxtLength   Txt        ISNUMERIC
---------------  ----------  ---------  ----------
NULL             0                      0
32               0                      0
8364             1           €          1
36               1           $          1
36               2           $$         0
8356             1           ₤          1
8352             1           ₠          0  --??
8358             1           ₦          0  --??
65020            1           ﷼          0  --??
43               1           +          1
45               1           -          1
47               1           /          0
92               1           \          1  --??
95               1           _          0
101              1           e          0
49               2           1e         0
101              2           e1         0
49               3           1e1        1
49               1           1          1
45               2           -1         1
43               2           +1         1
49               3           1+1        0
9352             1           ⒈         0
55356            2                     0
185              1           ¹          0
9312             1           ①          0
189              1           ½          0
55356            2                    0
57               307        /*...*/     1
57               308        /*...*/     1  --??
57               309        /*...*/     0  --??
57               310        /*...*/     0

The detailed behaviours of ISNUMERIC are not documented, and probably not fully known to anyone without source code access. That said, it may be that interpretation depends on the Unicode categorization (numeric or not). Equally, the weird cases you mention may be bugs that are preserved for backwards compatibility. Yes I know that sounds crazy, but it does happen.

As you are using SQL Server 2012, there is no need to use ISNUMERIC. Use TRY_CONVERT or the synonymous TRY_CAST instead to check if a string is convertible to a given type. Where they provide adequate functionality, these are preferable to TRY_PARSE, because the latter involves more expensive processing via CLR integration.


Create Random Word to obfuscate a SQL Column Does anyone have any SQL code to automatically generate words (all letter, no number).

Is there any inline SQL to perform this on a column?

We are trying to obfuscate first/last name and other word columns in table.

This answer currently does not work on column, because it generates the same result within a column.

select top (@stringlength) char(abs(checksum(newid())) % 26 + ascii('A'))
from sys.all_objects
for xml path('')

I tried this, but functions are slow, inline queries can be faster, feel free to improve or rewrite below

CREATE VIEW [dbo].[RandomValueVw]
AS
SELECT RAND() as random_value

CREATE function dbo.RandomWordGenerate(@length int)
returns varchar(50)
as begin

    DECLARE
        @text nvarchar(255),
        @i int

    SET @i = 0
    SET @text = ''

    WHILE (@i < @length)
    BEGIN
        SET @text = @text + CHAR((select * from [dbo].[RandomValueVw]) * 26 + 65)
        SET @i = @i + 1
    END

    return @text

end

CRYPT_GEN_RANDOM is your friend for this kind of thing.  It creates a simple number of the length you desire, and is about as lightweight as you can get for a cryptographic strength random number.  RAND(), in contrast will always generate the same sequence of numbers when called with the same seed value, and as such is definitely not cryptographically secure.

Anyway, building from your prior question, we'll add a [First Name] column and a [Last Name] column, as in:

USE tempdb;
GO
DROP TABLE IF EXISTS dbo.MyTable;
CREATE TABLE dbo.MyTable
(
    MyTableID int NOT NULL
        CONSTRAINT PK_MyTable
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
    , [Date of Birth] date NOT NULL
    , [First Name] nvarchar(100) NOT NULL
    , [Last Name] nvarchar(100) NOT NULL
);
GO

Here, we'll insert a couple of test rows:

INSERT INTO dbo.MyTable ([Date of Birth], [First Name], [Last Name])
VALUES ('1990-01-01', 'Max', 'Vernon')
    , ('1989-01-01', 'Apple', 'Book');

SELECT *
FROM dbo.MyTable;

The results:

+-----------+---------------+------------+-----------+
| MyTableID | Date of Birth | First Name | Last Name |
+-----------+---------------+------------+-----------+
|         1 | 1990-01-01    | Max        | Vernon    |
|         2 | 1989-01-01    | Apple      | Book      |
+-----------+---------------+------------+-----------+

Now, we'll run the update statement against the table:

DECLARE @MinAge int;
DECLARE @MaxAge int;

SET @MinAge = 10;
SET @MaxAge = 20;
UPDATE dbo.MyTable
SET [Date of Birth] = DATEADD(DAY
    , (1 - (CONVERT(int, CRYPT_GEN_RANDOM(2)) % ((@MaxAge - @MinAge) * 365)))
    , CONVERT(date, DATEADD(YEAR, 1 - @MinAge, GETDATE()))
    )
    , [First Name] = CONVERT(nvarchar(100), CRYPT_GEN_RANDOM(10))
    , [Last Name] = CONVERT(nvarchar(100), CRYPT_GEN_RANDOM(10));

Now, if we look at the table contents, we see:

SELECT *
FROM dbo.MyTable;

+-----------+---------------+------------+-----------+
| MyTableID | Date of Birth | First Name | Last Name |
+-----------+---------------+------------+-----------+
|         1 | 2003-07-22    | ⢢蝤偕龿ື     | 槈ស㎫答덌  |
|         2 | 2008-09-13    | ❈敍봻坄   | ꈨ氕㽲박ે    |
+-----------+---------------+------------+-----------+

As you can see, all data has been quite drastically changed.

If you're not quite happy with the random characters generated in the above example, you can generate less confusion values using this replacement for the [First Name] and [Last Name] bits:

    , [First Name] = REPLICATE(CHAR(CONVERT(int, (CRYPT_GEN_RANDOM(1) % 26) + 65)), 10)
    , [Last Name] = REPLICATE(CHAR(CONVERT(int, (CRYPT_GEN_RANDOM(1) % 26) + 65)), 10);

This results in the following output:

+-----------+---------------+------------+------------+
| MyTableID | Date of Birth | First Name | Last Name  |
+-----------+---------------+------------+------------+
|         1 | 2005-08-13    | PPPPPPPPPP | DDDDDDDDDD |
|         2 | 2002-08-12    | QQQQQQQQQQ | LLLLLLLLLL |
+-----------+---------------+------------+------------+

If you really need human-readable words, you could use a word list to randomly replace the values with actual words.  I've used a small portion1 of the BIP-0039 English language wordlist in the example below; however you could easily use any suitable list of words.

DROP TABLE IF EXISTS dbo.WordList;

CREATE TABLE dbo.WordList
(
    Word varchar(20) NOT NULL
        CONSTRAINT PK_WordList
        PRIMARY KEY CLUSTERED
    , OrderBy AS (CONVERT(int, CRYPT_GEN_RANDOM(2)))
) ON [PRIMARY];

INSERT INTO dbo.WordList
VALUES ('abandon')
, ('ability')
, ('able')
, ('about')
, ('above')
, ('absent')
, ('absorb')
, ('abstract')
, ('absurd')
, ('abuse')
, ('access')
, ('accident')
, ('account')
, ('accuse')
, ('achieve')
, ('acid')
, ('acoustic')
, ('acquire')
, ('across')
, ('act')
, ('action')
, ('actor')
, ('actress')
, ('actual')
, ('adapt')
, ('add')
, ('addict')
, ('address')
, ('adjust')
, ('admit')
, ('adult')
, ('advance')
, ('advice')
, ('aerobic')
, ('affair')
, ('afford')
, ('afraid')
, ('again')
, ('age')
, ('agent')
, ('agree')
, ('ahead')
, ('aim')
, ('air')
, ('airport')
, ('aisle')
, ('alarm')
, ('album')
, ('alcohol')
, ('alert')
, ('alien')
, ('all')
, ('alley')
, ('allow')
, ('almost')
, ('alone')
, ('alpha')
, ('already')
, ('also')
, ('alter')
, ('always')
, ('amateur')
, ('amazing')
, ('among')
, ('amount')
, ('amused')
, ('analyst')
, ('anchor')
, ('ancient')
, ('anger')
, ('angle')
, ('angry')
, ('animal')
, ('ankle')
, ('announce')
, ('annual')
, ('another')
, ('answer')
, ('antenna')
, ('antique')
, ('anxiety')
, ('any')
, ('apart')
, ('apology')
, ('appear')
, ('apple')
, ('approve')
, ('april')
, ('arch')
, ('arctic')
, ('area')
, ('arena')
, ('argue')
, ('arm')
, ('armed')
, ('armor')
, ('army')
, ('around')
, ('arrange')
, ('arrest')
, ('arrive')
, ('arrow')
, ('art')
, ('artefact')
, ('artist')
, ('artwork')
, ('ask')
, ('aspect')
, ('assault')
, ('asset')
, ('assist')
, ('assume')
, ('asthma')
, ('athlete')
, ('atom')
, ('attack')
, ('attend')
, ('attitude')
, ('attract')
, ('auction')
, ('audit')
, ('august')
, ('aunt')
, ('author')
, ('auto')
, ('autumn')
, ('average')
, ('avocado')
, ('avoid')
, ('awake')
, ('aware')
, ('away')
, ('awesome')
, ('awful')
, ('awkward')
, ('axis')
, ('baby')
, ('bachelor')
, ('bacon')
, ('badge')
, ('bag')
, ('balance')
, ('balcony')
, ('ball')
, ('bamboo')
, ('banana')
, ('banner')
, ('bar')
, ('barely')
, ('bargain')
, ('barrel')
, ('base')
, ('basic')
, ('basket')
, ('battle')
, ('beach')
, ('bean')
, ('beauty')
, ('because')
, ('become')
, ('beef')
, ('before')
, ('begin')
, ('behave')
, ('behind')
, ('believe')
, ('below')
, ('belt')
, ('bench')
, ('benefit')
, ('best')
, ('betray')
, ('better')
, ('between')
, ('beyond')
, ('bicycle')
, ('bid')
, ('bike')
, ('bind')
, ('biology')
, ('bird')
, ('birth')
, ('bitter')
, ('black')
, ('blade')
, ('blame')
, ('blanket')
, ('blast')
, ('bleak')
, ('bless')
, ('blind')
, ('blood')
, ('blossom')
, ('blouse')
, ('blue')
, ('blur')
, ('blush')
, ('board')
, ('boat')
, ('body')
, ('boil')
, ('bomb')
, ('bone')
, ('bonus')
, ('book')
, ('boost')
, ('border')
, ('boring')
, ('borrow')
, ('boss')
, ('bottom')
, ('bounce')
, ('box')
, ('boy')
, ('bracket')
, ('brain')
, ('brand')
, ('brass')
, ('brave')
, ('bread')
, ('breeze')
, ('brick')
, ('bridge')
, ('brief')
, ('bright')
, ('bring')
, ('brisk')
, ('broccoli')
, ('broken')
, ('bronze')
, ('broom')
, ('brother')
, ('brown')
, ('brush')
, ('bubble')
, ('buddy')
, ('budget')
, ('buffalo')
, ('build')
, ('bulb')
, ('bulk')
, ('bullet')
, ('bundle')
, ('bunker')
, ('burden')
, ('burger')
, ('burst')
, ('bus')
, ('business')
, ('busy')
, ('butter')
, ('buyer')
, ('buzz')
, ('cabbage')
, ('cabin')
, ('cable')
, ('cactus')
, ('cage')
, ('cake')
, ('call')
, ('calm')
, ('camera')
, ('camp')
, ('can')
, ('canal')
, ('cancel')
, ('candy')
, ('cannon')
, ('canoe')
, ('canvas')
, ('canyon')
, ('capable')
, ('capital')
, ('captain')
, ('car')
, ('carbon')
, ('card')
, ('cargo')
, ('carpet')
, ('carry')
, ('cart')
, ('case')
, ('cash')
, ('casino')
, ('castle')
, ('casual')
, ('cat')
, ('catalog')
, ('catch')
, ('category')
, ('cattle')
, ('caught')
, ('cause')
, ('caution')
, ('cave')
, ('ceiling')
, ('celery')
, ('cement')
, ('census')
, ('century')
, ('cereal')
, ('certain')
, ('chair')
, ('chalk')
, ('champion')
, ('change')
, ('chaos')
, ('chapter')
, ('charge')
, ('chase')
, ('chat')
, ('cheap')
, ('check')
, ('cheese')
, ('chef')
, ('cherry')
, ('chest')
, ('chicken')
, ('chief')
, ('child')
, ('chimney')
, ('choice')
, ('choose')
, ('chronic')
, ('chuckle')
, ('chunk')
, ('churn')
, ('cigar')
, ('cinnamon')
, ('circle')
, ('citizen')
, ('city')
, ('civil')
, ('claim')
, ('clap')
, ('clarify')
, ('claw')
, ('clay')
, ('clean')
, ('clerk')
, ('clever')
, ('click')
, ('client')
, ('cliff')
, ('climb')
, ('clinic')
, ('clip')
, ('clock')
, ('clog')
, ('close')
, ('cloth')
, ('cloud')
, ('clown')
, ('club')
, ('clump')
, ('cluster')
, ('clutch')
, ('coach')
, ('coast')
, ('coconut')
, ('code')
, ('coffee')
, ('coil')
, ('coin')
, ('collect')
, ('color')
, ('column')
, ('combine')
, ('come')
, ('comfort')
, ('comic')
, ('common')
, ('company')
, ('concert')
, ('conduct')
, ('confirm')
, ('congress')
, ('connect')
, ('consider')
, ('control')
, ('convince')
, ('cook')
, ('cool')
, ('copper')
, ('copy')
, ('coral')
, ('core')
, ('corn')
, ('correct')
, ('cost')
, ('cotton')
, ('couch')
, ('country')
, ('couple')
, ('course')
, ('cousin')
, ('cover')
, ('coyote')
, ('crack')
, ('cradle')
, ('craft')
, ('cram')
, ('crane')
, ('crash')
, ('crater')
, ('crawl')
, ('crazy')
, ('cream')
, ('credit')
, ('creek')
, ('crew')
, ('cricket')
, ('crime')
, ('crisp')
, ('critic')
, ('crop')
, ('cross')
, ('crouch')
, ('crowd')
, ('crucial')
, ('cruel')
, ('cruise')
, ('crumble')
, ('crunch')
, ('crush')
, ('cry')
, ('crystal')
, ('cube')
, ('culture')
, ('cup')
, ('cupboard')
, ('curious')
, ('current')
, ('curtain')
, ('curve')
, ('cushion')
, ('custom')
, ('cute')
, ('cycle')
, ('dad')
, ('damage')
, ('damp')
, ('dance')
, ('danger')
, ('daring')
, ('dash')
, ('daughter')
, ('dawn')
, ('day')
, ('deal')
, ('debate')
, ('debris')
, ('decade')
, ('december')
, ('decide')
, ('decline')
, ('decorate')
, ('decrease')
, ('deer')
, ('defense')
, ('define')
, ('defy')
, ('degree')
, ('delay')
, ('deliver')
, ('demand')
, ('demise')
, ('denial')
, ('dentist')
, ('deny')
, ('depart')
, ('depend')
, ('deposit')
, ('depth')
, ('deputy')
, ('derive')
, ('describe')
, ('desert')
, ('design')
, ('desk')
, ('despair')
, ('destroy')
, ('detail')
, ('detect')
, ('develop')
, ('device')
, ('devote')
, ('diagram')
, ('dial')
, ('diamond')
, ('diary')
, ('dice')
, ('diesel')
, ('diet')
, ('differ')
, ('digital')
, ('dignity')
, ('dilemma')
, ('dinner')
, ('dinosaur')
, ('direct')
, ('dirt')
, ('disagree')
, ('discover')
, ('disease')
, ('dish')
, ('dismiss')
, ('disorder')
, ('display')
, ('distance')
, ('divert')
, ('divide')
, ('divorce')
, ('dizzy')
, ('doctor')
, ('document')
, ('dog')
, ('doll')
, ('dolphin')
, ('domain')
, ('donate')
, ('donkey')
, ('donor')
, ('door')
, ('dose')
, ('double')
, ('dove')
, ('draft')
, ('dragon')
, ('drama')
, ('drastic')
, ('draw')
, ('dream')
, ('dress')
, ('drift')
, ('drill')
, ('drink')
, ('drip')
, ('drive')
, ('drop')
, ('drum')
, ('dry')
, ('duck')
, ('dumb')
, ('dune')
, ('during')
, ('dust')
, ('dutch')
, ('duty')
, ('dwarf')
, ('dynamic')
, ('eager')
, ('eagle')
, ('early')
, ('earn')
, ('earth')
, ('easily')
, ('east')
, ('easy')
, ('echo')
, ('ecology')
, ('economy')
, ('edge')
, ('edit')
, ('educate')
, ('effort')
, ('egg')
, ('eight')
, ('either')
, ('elbow')
, ('elder')
, ('electric')
, ('elegant')
, ('element')
, ('elephant')
, ('elevator')
, ('elite')
, ('else')
, ('embark')
, ('embody')
, ('embrace')
, ('emerge')
, ('emotion')
, ('employ')
, ('empower')
, ('empty')
, ('enable')
, ('enact')
, ('end')
, ('endless')
, ('endorse')
, ('enemy')
, ('energy')
, ('enforce')
, ('engage')
, ('engine')
, ('enhance')
, ('enjoy')
, ('enlist')
, ('enough')
, ('enrich')
, ('enroll')
, ('ensure')
, ('enter')
, ('entire')
, ('entry')
, ('envelope')
, ('episode')
, ('equal')
, ('equip')
, ('era')
, ('erase')
, ('erode')
, ('erosion')
, ('error')
, ('erupt')
, ('escape')
, ('essay')
, ('essence')
, ('estate')
, ('eternal')
, ('ethics')
, ('evidence')
, ('evil')
, ('evoke')
, ('evolve')
, ('exact')
, ('example')
, ('excess')
, ('exchange')
, ('excite')
, ('exclude')
, ('excuse')
, ('execute')
, ('exercise')
, ('exhaust')
, ('exhibit')
, ('exile')
, ('exist')
, ('exit')
, ('exotic')
, ('expand')
, ('expect')
, ('expire')
, ('explain')
, ('expose')
, ('express')
, ('extend')
, ('extra')
, ('eye')
, ('eyebrow')
, ('fabric');

INSERT INTO dbo.WordList
VALUES ('lab')
, ('label')
, ('labor')
, ('ladder')
, ('lady')
, ('lake')
, ('lamp')
, ('language')
, ('laptop')
, ('large')
, ('later')
, ('latin')
, ('laugh')
, ('laundry')
, ('lava')
, ('law')
, ('lawn')
, ('lawsuit')
, ('layer')
, ('lazy')
, ('leader')
, ('leaf')
, ('learn')
, ('leave')
, ('lecture')
, ('left')
, ('leg')
, ('legal')
, ('legend')
, ('leisure')
, ('lemon')
, ('lend')
, ('length')
, ('lens')
, ('leopard')
, ('lesson')
, ('letter')
, ('level')
, ('liar')
, ('liberty')
, ('library')
, ('license')
, ('life')
, ('lift')
, ('light')
, ('like')
, ('limb')
, ('limit')
, ('link')
, ('lion')
, ('liquid')
, ('list')
, ('little')
, ('live')
, ('lizard')
, ('load')
, ('loan')
, ('lobster')
, ('local')
, ('lock')
, ('logic')
, ('lonely')
, ('long')
, ('loop')
, ('lottery')
, ('loud')
, ('lounge')
, ('love')
, ('loyal')
, ('lucky')
, ('luggage')
, ('lumber')
, ('lunar')
, ('lunch')
, ('luxury')
, ('lyrics')
, ('machine')
, ('mad')
, ('magic')
, ('magnet')
, ('maid')
, ('mail')
, ('main')
, ('major')
, ('make')
, ('mammal')
, ('man')
, ('manage')
, ('mandate')
, ('mango')
, ('mansion')
, ('manual')
, ('maple')
, ('marble')
, ('march')
, ('margin')
, ('marine')
, ('market')
, ('marriage')
, ('mask')
, ('mass')
, ('master')
, ('match')
, ('material')
, ('math')
, ('matrix')
, ('matter')
, ('maximum')
, ('maze')
, ('meadow')
, ('mean')
, ('measure')
, ('meat')
, ('mechanic')
, ('medal')
, ('media')
, ('melody')
, ('melt')
, ('member')
, ('memory')
, ('mention')
, ('menu')
, ('mercy')
, ('merge')
, ('merit')
, ('merry')
, ('mesh')
, ('message')
, ('metal')
, ('method')
, ('middle')
, ('midnight')
, ('milk')
, ('million')
, ('mimic')
, ('mind')
, ('minimum')
, ('minor')
, ('minute')
, ('miracle')
, ('mirror')
, ('misery')
, ('miss')
, ('mistake')
, ('mix')
, ('mixed')
, ('mixture')
, ('mobile')
, ('model')
, ('modify')
, ('mom')
, ('moment')
, ('monitor')
, ('monkey')
, ('monster')
, ('month')
, ('moon')
, ('moral')
, ('more')
, ('morning')
, ('mosquito')
, ('mother')
, ('motion')
, ('motor')
, ('mountain')
, ('mouse')
, ('move')
, ('movie')
, ('much')
, ('muffin')
, ('mule')
, ('multiply')
, ('muscle')
, ('museum')
, ('mushroom')
, ('music')
, ('must')
, ('mutual')
, ('myself')
, ('mystery')
, ('myth')
, ('naive')
, ('name')
, ('napkin')
, ('narrow')
, ('nasty')
, ('nation')
, ('nature')
, ('near')
, ('neck')
, ('need')
, ('negative')
, ('neglect')
, ('neither')
, ('nephew')
, ('nerve')
, ('nest')
, ('net')
, ('network')
, ('neutral')
, ('never')
, ('news')
, ('next')
, ('nice')
, ('night')
, ('noble')
, ('noise')
, ('nominee')
, ('noodle')
, ('normal')
, ('north')
, ('nose')
, ('notable')
, ('note')
, ('nothing')
, ('notice')
, ('novel')
, ('now')
, ('nuclear')
, ('number')
, ('nurse')
, ('nut')
, ('oak')
, ('obey')
, ('object')
, ('oblige')
, ('obscure')
, ('observe')
, ('obtain')
, ('obvious')
, ('occur')
, ('ocean')
, ('october')
, ('odor')
, ('off')
, ('offer')
, ('office')
, ('often')
, ('oil')
, ('okay')
, ('old')
, ('olive')
, ('olympic')
, ('omit')
, ('once')
, ('one')
, ('onion')
, ('online')
, ('only')
, ('open')
, ('opera');

INSERT INTO dbo.WordList
VALUES ('vacant')
, ('vacuum')
, ('vague')
, ('valid')
, ('valley')
, ('valve')
, ('van')
, ('vanish')
, ('vapor')
, ('various')
, ('vast')
, ('vault')
, ('vehicle')
, ('velvet')
, ('vendor')
, ('venture')
, ('venue')
, ('verb')
, ('verify')
, ('version')
, ('very')
, ('vessel')
, ('veteran')
, ('viable')
, ('vibrant')
, ('vicious')
, ('victory')
, ('video')
, ('view')
, ('village')
, ('vintage')
, ('violin')
, ('virtual')
, ('virus')
, ('visa')
, ('wild')
, ('will')
, ('win')
, ('window')
, ('wine')
, ('wing')
, ('wink')
, ('winner')
, ('winter')
, ('wire')
, ('wisdom')
, ('wise')
, ('wish')
, ('witness')
, ('wolf')
, ('woman')
, ('wonder')
, ('wood')
, ('wool')
, ('word')
, ('work')
, ('world')
, ('worry')
, ('worth')
, ('wrap')
, ('wreck')
, ('wrestle')
, ('wrist')
, ('write')
, ('wrong')
, ('yard')
, ('year')
, ('yellow')
, ('you')
, ('young')
, ('youth')
, ('zebra')
, ('zero')
, ('zone')
, ('zoo');

To use the wordlist above, you could do something like this:

DECLARE @WordListCount int;
SELECT @WordListCount = COUNT(1)
FROM dbo.WordList;

UPDATE dbo.MyTable
SET dbo.MyTable.[First Name] = wlFirst.Word
    , dbo.MyTable.[Last Name] = wlLast.Word
FROM dbo.MyTable mt
    INNER JOIN (SELECT wl.Word, rn = ROW_NUMBER() OVER (ORDER BY wl.OrderBy) FROM dbo.WordList wl) wlFirst ON (mt.MyTableID % @WordListCount) = wlFirst.rn
    INNER JOIN (SELECT wl.Word, rn = ROW_NUMBER() OVER (ORDER BY wl.OrderBy) FROM dbo.WordList wl) wlLast ON (mt.MyTableID % @WordListCount) = wlLast.rn
GO

The results:

SELECT *
FROM dbo.MyTable;

╔═══════════╦═══════════════╦════════════╦═══════════╗
║ MyTableID ║ Date of Birth ║ First Name ║ Last Name ║
╠═══════════╬═══════════════╬════════════╬═══════════╣
║         1 ║ 1990-01-01    ║ kidney     ║ copy      ║
║         2 ║ 1989-01-01    ║ input      ║ mirror    ║
╚═══════════╩═══════════════╩════════════╩═══════════╝

1 - I cannot include the entire BIP-0039 wordlist here since the body of an answer is limited to 30,000 characters