Is there a tool like Microsoft's "SQL Server Profiler" for MySQL?

MySQL has never come up with Query Profiling. Now that MySQL is being grandfathered by Oracle, I know this will continue to be the case.

Yet, all hope is not lost.

Since 2007, Percona has come up with some absolutely marvelous tools for everything a Developer and DBA would want, including Query Profiling.

Percona's first set of tools, known as MAATKIT, created a realm for the serious user of MySQL. It features many things, such as:

Query Profiling
Replication Heartbeat
Replication Slave Management
Table Checksum and Synchronization

Percona has recently forked MAATKIT into a more up-to-date set of tools, known today as Percona Toolkit. These tools picked up where MAATKIT left off by expanding the realm of activity for the serious MySQL user to include things such:

Foreign Key Error Checking
Online Schema Changing
Visual Explain Plans
and more ...

Getting back to the original question, the tools out there for query profiling are

pt-query-advisor
pt-query-digest
mk-query-profiler (for MAATKIT users)
mk-query-digest (for MAATKIT users)

Here is an example of the kind of rich information that can come from using one of these tools:

I helped a client implement mk-query-digest to report the 20 worst-performing queries every 20 minutes. I got the idea from this YouTube video. The client would move any bad query's output to memcached thus lowering the incidence of the query's taking a toll on the the database.

Here is the script I made to call mk-query-digest (examing the processlist only)

#!/bin/sh

RUNFILE=/tmp/QueriesAreBeingDigested.txt
if [ -f ${RUNFILE} ] ; then exit ; fi

MKDQ=/usr/local/sbin/mk-query-digest
RUNTIME=${1}
COPIES_TO_KEEP=${2}
DBVIP=${3}

WHICH=/usr/bin/which
DATE=`${WHICH} date`
ECHO=`${WHICH} echo`
HEAD=`${WHICH} head`
TAIL=`${WHICH} tail`
AWK=`${WHICH} awk`
SED=`${WHICH} sed`
CAT=`${WHICH} cat`
WC=`${WHICH} wc`
RM=`${WHICH} rm | ${TAIL} -1 | ${AWK} '{print $1}'`
LS=`${WHICH} ls | ${TAIL} -1 | ${AWK} '{print $1}'`

HAS_THE_DBVIP=`/sbin/ip addr show | grep "scope global secondary" | grep -c "${DBVIP}"`
if [ ${HAS_THE_DBVIP} -eq 1 ] ; then exit ; fi

DT=`${DATE} +"%Y%m%d_%H%M%S"`
UNIQUETAG=`${ECHO} ${SSH_CLIENT}_${SSH_CONNECTION}_${DT} | ${SED} 's/\./ /g' | ${SED} 's/ //g'`

cd /root/QueryDigest
OUTFILE=QP_${DT}.txt
HOSTADDR=${DBVIP}
${MKDQ} --processlist h=${HOSTADDR},u=queryprofiler,p=queryprofiler --run-time=${RUNTIME} > ${OUTFILE}

#
# Rotate out Old Copies
#

QPFILES=QPFiles.txt
QPFILES2ZAP=QPFiles2Zap.txt
${LS} QP_[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]_[0-9][0-9][0-9][0-9][0-9][0-9].txt > ${QPFILES}

LINECOUNT=`${WC} -l < ${QPFILES}`
if [ ${LINECOUNT} -gt ${COPIES_TO_KEEP} ]
then
        (( DIFF = LINECOUNT - COPIES_TO_KEEP ))
        ${HEAD} -${DIFF} < ${QPFILES} > ${QPFILES2ZAP}
        for QPFILETOZAP in `${CAT} ${QPFILES2ZAP}`
        do
                ${RM} ${QPFILETOZAP}
        done
fi

rm -f ${QPFILES2ZAP}
rm -f ${QPFILES}
rm -f ${RUNFILE}

Here is the user I made to connect to mysql using mk-query-digest

GRANT PROCESS ON *.* TO 'queryprofiler'@'%' IDENTIFIED BY 'queryprofiler';

Here is the crontab I ran every 20 minutes (less 10 seconds) keeping the last 144 copies (which is 48 hours of profiling)

*/20 * * * * /root/QueryDigest/ExecQueryDigest.sh 1190s 144 10.1.1.8

The incredible part: The output of mk-query-digest

Here is a profile that ran 2011-12-28 11:20:00 for 1190 sec (20 min less 10 sec)

The last 22 lines

# Rank Query ID           Response time    Calls   R/Call     Item
# ==== ================== ================ ======= ========== ====
#    1 0x5E994008E9543B29    40.3255 11.2%     101   0.399263 SELECT schedule_occurrence schedule_eventschedule schedule_event schedule_eventtype schedule_event schedule_eventtype schedule_occurrence.start
#    2 0x392F6DA628C7FEBD    33.9181  9.4%      17   1.995184 SELECT mt_entry mt_objecttag
#    3 0x6C6318E56E149036    26.4695  7.3%     102   0.259505 SELECT schedule_occurrence schedule_eventschedule schedule_event schedule_eventtype schedule_event schedule_eventtype schedule_occurrence.start
#    4 0x00F66961DAE6FFB2    25.5472  7.1%      55   0.464495 SELECT mt_entry mt_placement mt_category
#    5 0x99E13015BFF1E75E    22.3618  6.2%     199   0.112371 SELECT mt_entry mt_objecttag
#    6 0x84DD09F0FC444677    22.3516  6.2%      39   0.573118 SELECT mt_entry
#    7 0x440EBDBCEDB88725    21.1817  5.9%      36   0.588380 SELECT mt_entry
#    8 0x8D258C584B858811    17.2402  4.8%      37   0.465951 SELECT mt_entry mt_placement mt_category
#    9 0x4E2CB0F4CAFD1400    16.9768  4.7%      40   0.424419 SELECT mt_entry mt_placement mt_category
#   10 0x377E0D0898266FDD    16.6979  4.6%     150   0.111319 SELECT polls_pollquestion mt_category
#   11 0x3B9686D98BB8E054    16.2089  4.5%      32   0.506529 SELECT mt_entry mt_objecttag mt_tag
#   12 0x97F670B604A85608    15.6158  4.3%      34   0.459287 SELECT mt_entry mt_placement mt_category
#   13 0x3F5557DA231225EB    14.4309  4.0%      36   0.400859 SELECT mt_entry mt_placement mt_category
#   14 0x191D660A10738896    13.1220  3.6%      31   0.423290 SELECT mt_entry mt_placement mt_category
#   15 0xF88F7421DD88036D    12.1261  3.4%      61   0.198788 SELECT mt_entry mt_blog mt_objecttag mt_tag mt_author
#   16 0xA909BF76E7051792    10.3971  2.9%      53   0.196172 SELECT mt_entry mt_objecttag mt_tag
#   17 0x3D42D07A335ED983     9.1424  2.5%      20   0.457121 SELECT mt_entry mt_placement mt_category
#   18 0x59F43B57DD43F2BD     9.0533  2.5%      21   0.431111 SELECT mt_entry mt_placement mt_category
#   19 0x7961BD4C76277EB7     8.5564  2.4%      47   0.182052 INSERT UNION UPDATE UNION mt_session
#   20 0x173EB4903F3B6DAC     8.5394  2.4%      22   0.388153 SELECT mt_entry mt_placement mt_category

Notice that this the list of the 20 worst-performing queries based on Query Response Time divided by the Number of Times the query was called.

Looking at Query ID #1, which is 0x5E994008E9543B29, we locate that Query ID in the output file and here is the report for that particular query:

# Query 1: 0.09 QPS, 0.03x concurrency, ID 0x5E994008E9543B29 at byte 0 __
# This item is included in the report because it matches --limit.
#              pct   total     min     max     avg     95%  stddev  median
# Count          4     101
# Exec time      7     40s   303ms      1s   399ms   992ms   198ms   293ms
# Lock time      0       0       0       0       0       0       0       0
# Users                  1      mt
# Hosts                101 10.64.95.73:33750 (1), 10.64.95.73:34452 (1), 10.64.95.73:38440 (1)... 97 more
# Databases              1     mt1
# Time range 1325089201 to 1325090385
# bytes          0 273.60k   2.71k   2.71k   2.71k   2.62k       0   2.62k
# id             4 765.11M   7.57M   7.58M   7.58M   7.29M    0.12   7.29M
# Query_time distribution
#   1us
#  10us
# 100us
#   1ms
#  10ms
# 100ms  ################################################################
#    1s  ######
#  10s+
# Tables
#    SHOW TABLE STATUS FROM `mt1` LIKE 'schedule_occurrence'\G
#    SHOW CREATE TABLE `mt1`.`schedule_occurrence`\G
#    SHOW TABLE STATUS FROM `mt1` LIKE 'schedule_eventschedule'\G
#    SHOW CREATE TABLE `mt1`.`schedule_eventschedule`\G
#    SHOW TABLE STATUS FROM `mt1` LIKE 'schedule_event'\G
#    SHOW CREATE TABLE `mt1`.`schedule_event`\G
#    SHOW TABLE STATUS FROM `mt1` LIKE 'schedule_eventtype'\G
#    SHOW CREATE TABLE `mt1`.`schedule_eventtype`\G
#    SHOW TABLE STATUS FROM `schedule_occurrence` LIKE 'start'\G
#    SHOW CREATE TABLE `schedule_occurrence`.`start`\G
# EXPLAIN
SELECT `schedule_occurrence`.`id`, `schedule_occurrence`.`schedule_id`, `schedule_occurrence`.`event_id`, `schedule_occurrence`.`start`, `schedule_occurrence`.`end`, `schedule_occurrence`.`cancelled`, `schedule_occurrence`.`original_start`, `schedule_occurrence`.`original_end`, `schedule_occurrence`.`all_day`, `schedule_occurrence`.`ongoing`, `schedule_occurrence`.`featured`, `schedule_eventschedule`.`id`, `schedule_eventschedule`.`event_id`, `schedule_eventschedule`.`start`, `schedule_eventschedule`.`end`, `schedule_eventschedule`.`all_day`, `schedule_eventschedule`.`ongoing`, `schedule_eventschedule`.`min_date_calculated`, `schedule_eventschedule`.`max_date_calculated`, `schedule_eventschedule`.`rule`, `schedule_eventschedule`.`end_recurring_period`, `schedule_eventschedule`.`textual_description`, `schedule_event`.`id`, `schedule_event`.`title`, `schedule_event`.`slug`, `schedule_event`.`description`, `schedule_event`.`host_id`, `schedule_event`.`cost`, `schedule_event`.`age_restrictions`, `schedule_event`.`more_info`, `schedule_event`.`photo_id`, `schedule_event`.`contact_email`, `schedule_event`.`event_type_id`, `schedule_event`.`featured`, `schedule_event`.`staff_pick`, `schedule_event`.`futuremost`, `schedule_event`.`creator_id`, `schedule_event`.`created_on`, `schedule_event`.`allow_comments`, `schedule_event`.`mt_entry`, `schedule_eventtype`.`id`, `schedule_eventtype`.`parent_id`, `schedule_eventtype`.`name`, `schedule_eventtype`.`slug`, `schedule_eventtype`.`lft`, `schedule_eventtype`.`rght`, `schedule_eventtype`.`tree_id`, `schedule_eventtype`.`level`, T5.`id`, T5.`title`, T5.`slug`, T5.`description`, T5.`host_id`, T5.`cost`, T5.`age_restrictions`, T5.`more_info`, T5.`photo_id`, T5.`contact_email`, T5.`event_type_id`, T5.`featured`, T5.`staff_pick`, T5.`futuremost`, T5.`creator_id`, T5.`created_on`, T5.`allow_comments`, T5.`mt_entry`, T6.`id`, T6.`parent_id`, T6.`name`, T6.`slug`, T6.`lft`, T6.`rght`, T6.`tree_id`, T6.`level` FROM `schedule_occurrence` INNER JOIN `schedule_eventschedule` ON (`schedule_occurrence`.`schedule_id` = `schedule_eventschedule`.`id`) INNER JOIN `schedule_event` ON (`schedule_eventschedule`.`event_id` = `schedule_event`.`id`) INNER JOIN `schedule_eventtype` ON (`schedule_event`.`event_type_id` = `schedule_eventtype`.`id`) INNER JOIN `schedule_event` T5 ON (`schedule_occurrence`.`event_id` = T5.`id`) INNER JOIN `schedule_eventtype` T6 ON (T5.`event_type_id` = T6.`id`) WHERE (EXTRACT(MONTH FROM `schedule_occurrence`.`start`) = 8 AND EXTRACT(DAY FROM `schedule_occurrence`.`start`) = 6 AND `schedule_occurrence`.`start` BETWEEN '2011-01-01 00:00:00' and '2011-12-31 23:59:59.99') ORDER BY `schedule_occurrence`.`ongoing` ASC, `schedule_occurrence`.`all_day` DESC, `schedule_occurrence`.`start` ASC\G

Although the histogram is text-based, it gives an accurate picture of the query's overall performance, sometimes running over 1 sec, and most of the time between 0.01 and 0.1 seconds. From here, one can proceed to do performance tuning by refactoring the query, placing query results in memcached, adding missing or covering indexes, etc.

CONCLUSION

IMHO If Percona ever placed the profiler tools into a Windows GUI, it would easily rival Microsoft's SQL Server Profiler.

Defense Rests !!!


I am happy to report (in case it helps anyone else) that v2 of pgAdmin 4 addresses this issue by providing a close widget in each toast that comes up.


How to store single result in variable and reuse it in a query (Oracle)?

For Toad, from this answer on SO:

  I think this will accomplish what you want.  You can declare a bind variable, insert a value into it, and then use it in future statements.

variable l_var varchar2(1);

begin
  select dummy
    into :l_var
    from dual;
end;

select *
from dual
where dummy = :l_var;


SQL Server transactional replication issue with database

The schema on the left is your own DDL statement. The schema on the right is a DDL statement generated by your schema comparison tool.

The statement on the right shows constraint names for your DEFAULT constraints. Yours does not have them but that does not mean SQL Server allows you to create a nameless constraint. When you do not provide a name explicitly, SQL Server picks one for you, because ultimately a constraint must have a name. The names on the right are the ones chosen by SQL Server.

So, the two sets of constraints most likely are the same in both databases. It is only the DDL statements that differ, because yours does not assign constraint names.

You can assign your own names if you use the syntax shown in the query on the right. That is, instead of declaring a default value like this:

DEFAULT (default_value)

use the following syntax:

CONSTRAINT constraint_name DEFAULT (default_value)

That way the names will always be predictable and, when comparing the schemas, you will not run into this issue again.

The same applies to all types of constraints in SQL Server (defaults, primary keys, foreign keys, check constraints).


For every new MySQL connection from a Client, some amount of memory will be used. So once the number of connections (which uses RAM) exceeds, you will be getting this error.

So check your application whether the connections are properly closed after using.

Also check what is the max_used_connections status variable and check whether that many connections are really getting connected from application. If application is not using that many number of connections, then it is definitely connection leak.


There is no need to use VALUES; simplify the insert to

INSERT INTO t (class_id, teacher_id, student_id)
    SELECT id, 1, 1 FROM ...

To get rid of extra columns, make an extra level of SELECT:

SELECT a, b
    FROM (
        SELECT a, extra, b FROM ...
         ) AS x

But, you probably won't have any extra columns if you do

ORDER BY
    IF (`name` = "John Smith", 2,
        IF (`name` RLIKE "[[:<:]]John Smith[[:>:]]", 1,
            MATCH (`name`) AGAINST ("John Smith")
       )   )


Community wiki answer generated from comments on the question by Markus W Mahlberg:

You might want to use bulk operations during insert. In case not all your RAM is used, it is safe to assume that either the disks or the inserting program is the limiting factor. With bulk ops, you speed up things for both limiting factors. There is something seriously wrong. Unless your fields are very big, 200k inserts/h are a mere joke. You want to have your code peer reviewed and make sure that IO operations are efficient. See the production notes for further details on this.

There are bulk upserts: bulk.find({...}).update({...},{upsert:true}). Furthermore, you can do var bulk=db.collection.initializeOrderedBulkOp().


Consistent mysqldump of mixed InnoDB and MyISAM tables from RDS instance

Last month, I answered this question : Synchronizing a RDS instance with another RDS instance

From researching and answering that post, I recently learned that you can set read_only off on a MySQL RDS Slave. What can this mean for you ? A very unique opportunity. What opportunity ?

CONVERT ALL YOUR MyISAM TABLES TO InnoDB !!!

Once you have made the RDS Slave writable by setting read_only to 0, you can change the storage engine of all the tables in the RDS Slave to InnoDB. Don't worry, it is possible for a Master to have MyISAM tables and the Slave to have InnoDB. I have suggested Master and Slave having different storage engines before (Can I have an InnoDB master and MyISAM slaves with Full-Text for searching?)

Once you have converted all that tables in the RDS Slave to InnoDB, you make read replicas of that new Slave without the MyISAM headaches of backing up data.

NOTE : Here is a sample script to convert all your MyISAM tables to InnoDB

MYSQL_USER=root
MYSQL_PASS=rootpassword
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
CONVERT_SCRIPT=/tmp/ConvertMyISAMToInnoDB.sql
echo "SET SQL_LOG_BIN = 0;" > ${CONVERT_SCRIPT}
SQL="SELECT CONCAT('ALTER TABLE ',table_schema,'.',table_name,' ENGINE=InnoDB;')"
SQL="${SQL} FROM information_schema.tables WHERE engine='MyISAM' AND"
SQL="${SQL} table_schema NOT IN ('information_schema','mysql','performance_schema')"
mysql ${MYSQL_CONN} -ANe"${SQL}" > ${CONVERT_SCRIPT}
less ${CONVERT_SCRIPT}

GIVE IT A TRY !!!


How to group joined tables by aggregate function, specifically by average group-name type?

ORA-00979 not a GROUP BY expression

  Cause: The GROUP BY clause does not contain all the expressions in the SELECT clause. SELECT expressions that are not included in a group function, such as AVG, COUNT, MAX, MIN, SUM, STDDEV, or VARIANCE, must be listed in the GROUP BY clause.
  Action: Include in the GROUP BY clause all SELECT expressions that are not group function arguments.

Gt.description column is not listed in the group by clause. Columns not participating in group function should be listed in group by clause.

SELECT gt.grade_type_code,
       gt.description,
       round(avg(gg.numeric_grade),2) AS "Average Grade"
FROM student.grade_type gt
JOIN student.grade gg ON gt.grade_type_code = gg.GRADE_TYPE_CODE
GROUP BY gt.grade_type_code,
         gt.description
ORDER BY gt.grade_type_code;


Problem setting MSDTC on client computer

This ended out being a domain policy issue, the computer was moved to an other group and MSDTC was registered and working successfully.


Use insert ... on conflict

insert into table_Name
  (id, "key", value)
values
  (10, 'key_1', 60),
  (11, 'key_2', 10)
on conflict (id, "key") do update
  set value = excluded.value;

This assumes that you have a unique key (or index) on table_name(id, "key")

Without a unique constraint or index you can do it like this:

WITH new_values (id, "key", value) as (
  values
  (10, 'key_1', 60),
  (11, 'key_2', 10)

), upsert as (
    update the_table t
        set value = c.value
    FROM new_values nv
    WHERE t.id = nv.id
      AND t."key" = nv."key"
    RETURNING t.*
)
INSERT INTO the_table (id, "key", value)
SELECT nv.id, nv."key", nv.value
FROM new_values nv
WHERE NOT EXISTS (SELECT 1
                  FROM upsert up
                  WHERE up.id = nv.id
                    AND up."key" = nv."key")
;

The above is NOT safe for concurrent insert/updates - you can still wind up with duplicates. The only reliable way to avoid those duplicates is to use a unique constraint or index.


Where to find the OID of a newly created Schema

Do NOT change the system catalogs manually!

If you want to move a table to a different schema, there is no need to know the  oid of the table or the schema:
As documented in the manual you can easily do this through ALTER TABLE
ALTER TABLE old_schema.some_table
    SET SCHEMA new_schema;

without having to check or know any oid.


We have a 3.5 TB DB which is already involved in log shipping to two different server, as one will be in restore sate only and one will be in stand by mode which helps for reporting need. all source and destinations are sql 2005.

Make sure you have Instant File Initialization ON. This helps to drastically cut down the backup/restore times.

  we took a differential backup to of primary (since we have to many log backups which run every 15 mins) and restored it and started log shipping. Here it fails by skipping the log file since it is looking for previous log file which is already shipped to other servers.

A differential or FULL backup does not break the log chain. Also, logshipping would not skip the files. There has to be something that might be taking ad-hoc log backup and then breaking logshipping.

I have set up a 3 way logshipping (dont know what to call .. so calling 3 way - one primary ships to 3 other standby servers) and it works without any issues.

  Finally we stopped log backups and followed the same procedure it succeeded but it took 3 hours downtime which we don’t want to do since it is a prod server.

As I mentioned earlier, Instant File Initialization will help with the restoring of full backup - cutting down the restore time.

Setting up log-shipping won't involve a downtime on your primary server. It will only on your secondary server - if you are doing reporting and this is your case. It is by design, that during the restore - you cannot access the database.


How do I find the longest string in a row amongst various text fields?

The following code should return a single column that has the largest piece of text in the current database, along with the Schema, Table, and Column name, and the size of the piece of text:

DECLARE @cmd NVARCHAR(max);
DECLARE @sep NVARCHAR(max);
SET @cmd = '';
SET @sep = '';
SELECT @cmd = @cmd + @sep +
    'SELECT ''' + QUOTENAME(sc.name) + '.' + QUOTENAME(t.name) +
    '.' + QUOTENAME(c.name) + ''' AS ColumnName, ' +
    QUOTENAME(c.name) + ' AS ColumnValue, LEN(CAST(' +
    QUOTENAME(c.name) + ' AS NVARCHAR(max))) AS ColumnLength ' +
    ' FROM ' + QUOTENAME(sc.name) + '.' + QUOTENAME(t.name)
    , @sep = ' UNION ALL '
FROM sys.tables t
    INNER JOIN sys.columns c ON t.object_id = c.object_id
    INNER JOIN sys.types ty on c.system_type_id = ty.system_type_id
    INNER JOIN sys.schemas sc ON t.schema_id = sc.schema_id
WHERE t.is_ms_shipped = 0
    AND ty.name IN (
        'ntext'
        , 'text'
        , 'varchar'
        , 'nvarchar'
        , 'nchar'
        , 'char'
        , 'sysname'
        , 'sql_variant'
        );
SET @cmd = 'SELECT TOP(1) * FROM (' + @cmd + ') t ORDER BY 3 DESC'
SELECT @cmd; /* This displays the resulting SQL Text that will be
            EXEC'd by the following statement */
EXEC sp_executesql @cmd;


So I found solution, --drop shoud be added in the command:

mongodump -h $HOST:$PORT --ssl --sslAllowInvalidCertificates --authenticationDatabase $authdb -u $user -p $pass --drop --gzip --archive=$destination


Why can't I obtain OBJECT_NAME() for a temporary table?

You have to tell object_name that it needs to look in the metadata of tempdb.

select object_name(object_id('tempdb..#t'), db_id('tempdb')) as from_beta1


Guidance on inter-server database or data transfer and system architecture

You could use Availability Groups for this, which I've done in the past. You would have 20 AGs, and each would have the query server as a replica, probably an async replica. You could also use replication for this, especially since it's unlikely that the users need all tables in every database. I don't like supporting replication though. Maybe the query server would be a Data Warehouse.


SQL Server Client configuration via file

The data about connection aliases is stored in the Registry, so either a .reg file or a script that updates registry would do. Updating the Registry can be done with reg.exe or Powershell.

The tricky parts are cliconfg syntax and the fact that 32/64 bit registry keys are not the same. That is,

HKLM\SOFTWARE\Microsoft\MSSQLServer\Client
HKLM\SOFTWARE\WOW6432Node\Microsoft\MSSQLServer\Client

Take a look at this article about 32/64 bit versions.


How to select a specific row based on another row from same table

Add a ROW_NUMBER(), and select all records where row_number = 1.

with dupli as
(
    select id, col1, col2, row_number() over (partition by id order by col1) rn
    from @dup
)
select     d1.*
from       @dup d1
inner join dupli d2
on         d1.id = d2.id
and        d1.col1 = d2.col1
and        d2.rn = 1;

|id    |col1|col2         |
|:-----|---:|:------------|
|111111|   1|Green        |
|222222|   9|Not Specified|
|333333|   1|Blue         |
|444444|   2|Undisclosed  |

dbfiddle here


Could anyone help to index the below Query

I am not sure do you want to create index key so you can create relations or force index keys on this statement but if you want to force it on this statement you can use this

SELECT *
FROM Table WITH(INDEX(Index_Name))

Hope this helps


You could force the SQL Server instance to cache a query plan for the statement or procedure that you think is missing from the query plan cache. Or you could verify that it is indeed a query plan cache issue or not.

Caching a Query Plan

This is accomplished with the stored procedure sp_create_plan_guide (Transact-SQL)

sp_create_plan_guide takes the following parameters:

sp_create_plan_guide [ @name = ] N'plan_guide_name'
    , [ @stmt = ] N'statement_text'
    , [ @type = ] N'{ OBJECT | SQL | TEMPLATE }'
    , [ @module_or_batch = ]
      {
                    N'[ schema_name. ] object_name'
        | N'batch_text'
        | NULL
      }
    , [ @params = ] { N'@parameter_name data_type [ ,...n ]' | NULL }
    , [ @hints = ] { N'OPTION ( query_hint [ ,...n ] )'
                 | N'XML_showplan'
                 | NULL }

Please read the online manual on how to use the various parameters.

Example on how you could cache the statement of a stored procedure:

exec sp_create_plan_guide
    @name = N'Forced_Plan_Guide_For_MyProc',
    @stmt = N'SELECT pi.product_name FROM product.items as pi WHERE pi.product_id = @product_id',
    @type = N'OBJECT',
    @batch_or_module = N'MyProc', --name of your procedure
    @params = NULL,
    @hints = N'OPTION (OPTIMIZE FOR (@product_id > 1000)';

This would create a query plan for the procedure named MyProc and optimize the query plan for all product.items which have a pi.product_id greater than 1000.

This is just an example on how to create a query plan for a procedure. It is slightly different for a stand-alone SQL Statement.

You can then edit this into a scheduled job and let it run once in the morning or whatever your preferences are.

Alternatives

The above solution will not result in the data being stored in SQL Server's data cache. You might be encountering an issue with data cache and not actually with the plan cache.

Check out Brent Ozar's sp_BlitzCache stored procedure to give your SQL Server's cache a quick check.

  Disclosure: Not affiliated with Brenz Ozar's company


You can identify problematic queries through DMVs using this query:

USE [master]
GO
SELECT  session_id,
blocking_session_id,
wait_time,
wait_type,
last_wait_type,
wait_resource,
transaction_isolati,
on_level,
lock_timeout
FROM sys.dm_exec_requests
WHERE blocking_session_id <> 0
GO

If you want to collect historical data or automate the process, you should consider 3rd party solutions. I’m using ApexSQL Monitor, but there are many others on the market. WindRaven mentioned some but there are also Idera Diagnostic, Nagios, SQL Sentry, Quest Spotlight.


Proper drive partitioning in Linux for Oracle

It all looks fairly logical from a historical point of view.  You generally want to separate out your redo, data, exports, backups etc to separate disks if you can.  You may find that these are all mounted from different physical disks which is the 'correct' way of doing things (e.g. you don't want your data and redo on the same disks if you can help it).

However, from my point of view it's a fairly "old school" build.

For the Oracle 11gR2 environments that I am currently building, I only have the following Oracle specific mounts:

/u01/ (oracle homes)
+FRA (ASM) _or_ /FRA (depending on requirements)
+DATA (ASM)
/backups/ (so that Netbackup plays nicely with RMAN backupsets)

I could split it up more, but seeing as everything's coming from the same SAN device and I can't persuade my infrastructure team to spend the time ensuring that they're all coming from different LUNs/physical disks, I'm not too worried yet.


Speeding Up Query on a Time Series Table. It's Slow to get (MAX - MIN) Aggregation Value

An index on (point_id, datetime, value) will likely speed up the query, as it will only have to do an index seek, read only the relevant part of the index and also have available (from the index) all the values of the value column to calculate the MIN and MAX.

An index on (A) (point_id) or (B) (point_id, datetime) would need to also find the read the relevant tuples from the table, in order (A) to filter them for the datetime range and (A and B) to read the values of the value column.


Determining the full postgresql size on windows

Part of the difficulty here depends on setup.  For this purpose I will assume a default setup.  I am also assuming here that we aren't counting system libraries here, or connection libraries, only PostgreSQL itself (i.e. no dependencies, and not libpq).

The best place to start is indeed the sum of the user folder plus the program files/postgresql folder.  If you are putting the logs elsewhere you will want to add those in as well.


SQL Server: How to track progress of CREATE INDEX command?

I think the following query will at least get you quite close. It makes use of a DMV that was introduced in SQL Server 2014: sys.dm_exec_query_profiles (and thanks to Martin Smith for introducing it to me via this related DBA.StackExchange Answer: Progress of SELECT INTO statement :-).

Please note:

!! You will need to add SET STATISTICS PROFILE ON; or SET STATISTICS XML ON; in the query batch that is doing the CREATE INDEX (and placed before the CREATE INDEX statement, if that wasn't obvious), else no rows will show up in this DMV for that SPID / session_id !!
The IN operator is used to filter out the Index Insert row that, if included, will increase the TotalRows values, which will skew the calculations since that row never shows any rows processed.
The row count displayed here (i.e. TotalRows) is double the row count of the table due to the operation taking two steps, each one operating on all of the rows: first is a "Table Scan" or "Clustered Index Scan", and second is the "Sort". You will see "Table Scan" when creating a Clustered Index or creating a NonClustered Index on a Heap. You will see "Clustered Index Scan" when creating a NonClustered Index on a Clustered Index.
This query does not seem to work when creating Filtered Indexes. For some reason, Filtered Indexes a) do not have the "Sort" step, and b) the row_count field never increases from 0.
Not sure what I was testing before, but my tests now indicate that Filtered Indexes are captured by this query. Sweet. Though just beware that row counts might be off (I will see if I can fix that someday).
When creating a Clustered Index on a Heap that already has NonClustered Indexes on it, the NonClustered Indexes need to be rebuilt (to swap out the RID -- RowID -- for the Clustered Index Key(s)), and each NonClustered Index rebuild will be a separate operation and hence not reflected in the stats returned by this query during the creation of the Clustered Index.
This query has been tested against:

Creating:

NonClustered Indexes on a Heap
a Clustered Index (no NonClustered Indexes exist)
NonClustered Indexes on the Clustered Index/Table
a Clustered Index when NonClustered Indexes already exist
Unique NonClustered Indexes on the Clustered Index/Table

Rebuilding (table with Clustered Index and one NonClustered Index; tested on SQL Server 2014, 2016, 2017, and 2019) via:

ALTER TABLE [schema_name].[table_name] REBUILD; (only Clustered Index shows up when using this method)
ALTER INDEX ALL ON [schema_name].[table_name] REBUILD;
ALTER INDEX [index_name] ON [schema_name].[table_name] REBUILD;

DECLARE @SPID INT = 51;

;WITH agg AS
(
     SELECT SUM(qp.[row_count]) AS [RowsProcessed],
            SUM(qp.[estimate_row_count]) AS [TotalRows],
            MAX(qp.last_active_time) - MIN(qp.first_active_time) AS [ElapsedMS],
            MAX(IIF(qp.[close_time] = 0 AND qp.[first_row_time] > 0,
                    [physical_operator_name],
                    N'<Transition>')) AS [CurrentStep]
     FROM sys.dm_exec_query_profiles qp
     WHERE qp.[physical_operator_name] IN (N'Table Scan', N'Clustered Index Scan',
                                           N'Index Scan',  N'Sort')
     AND   qp.[session_id] = @SPID
), comp AS
(
     SELECT *,
            ([TotalRows] - [RowsProcessed]) AS [RowsLeft],
            ([ElapsedMS] / 1000.0) AS [ElapsedSeconds]
     FROM   agg
)
SELECT [CurrentStep],
       [TotalRows],
       [RowsProcessed],
       [RowsLeft],
       CONVERT(DECIMAL(5, 2),
               (([RowsProcessed] * 1.0) / [TotalRows]) * 100) AS [PercentComplete],
       [ElapsedSeconds],
       (([ElapsedSeconds] / [RowsProcessed]) * [RowsLeft]) AS [EstimatedSecondsLeft],
       DATEADD(SECOND,
               (([ElapsedSeconds] / [RowsProcessed]) * [RowsLeft]),
               GETDATE()) AS [EstimatedCompletionTime]
FROM   comp;

Sample output:

                        Rows                 Percent   Elapsed  Estimated    Estimated
CurrentStep  TotalRows  Processed  RowsLeft  Complete  Seconds  SecondsLeft  CompletionTime
-----------  ---------  ---------  --------  --------  -------  -----------  --------------
Clustered    11248640   4786937    6461703   42.56     4.89400  6.606223     2016-05-23
Index Scan                                                                   14:32:40.547


It's dependent on the connector that you're using. For example, if using Connector/J then you can provide multiple server addresses in your connect-string... http://dev.mysql.com/doc/connector-j/en/connector-j-reference-configuration-properties.html

Andrew.


Oracle: list user-created tables in the SYS schema

Most tables in SYS schema are created when database is installed. You probably know approximately when the new tables were created, so query like this should work:

select *
from dba_objects
where object_type='TABLE'
  and owner='SYS'
  and created > <date before new tables were created>;


Excellent question. Yes, if LoginA (or UserA) is granted IMPERSONATE on LoginB (or UserB), then LoginA (or UserA) can execute the EXECUTE AS statement whenever they want, without restriction:

EXECUTE AS LOGIN = 'LoginB';

or

EXECUTE AS USER = 'UserB';

  then what is the purpose of a stored procedure with EXECUTE AS?

The purpose of the EXECUTE AS clause (part of the CREATE {module} statement) is to temporarily grant that IMPERSONATE permission only in the context of that module. This prevents you from needing to explicitly grant IMPERSONATE. The difference is, outside of the context of that module with EXECUTE AS {SomeUser}, whoever is executing that module shouldn't have the ability to impersonate that User. You don't want Logins / Users being able to impersonate others whenever they want. That is a security risk. The EXECUTE AS clause gives you the ability to let a principal impersonate another only when you want, not when they try to on their own (where you can't restrict what they do with those elevated permissions). So, you actually do want the EXECUTE AS clause, and do not want to grant IMPERSONATE to anyone ever (if it can be avoided, and most likely it can be).

For example: There is no TRUNCATE TABLE permission. If you want someone to be able to truncate a particular table (that they do not own, since they do have permission to do that) you could grant them IMPERSONATE on dbo. However, there is then no way to restrict their actions to only issuing TRUNCATE TABLE, and only on that particular table. That User can execute EXECUTE AS USER = 'dbo'; whenever they want, and then do whatever they want. However, by adding EXECUTE AS 'dbo' to the CREATE PROCEDURE .... AS TRUNCATE TABLE {TableName}; statement, whoever executes that module will still impersonate dbo, but only for the actions in that module, which here is just truncating a particular table.

Of course, the EXECUTE AS clause of a CREATE statement can only impersonate another User (database-level only). It cannot impersonate another Login (instance-level). This limitation is what leads too many people to enable TRUSTWORTHY so that a Login matching the same SID of the User being impersonated will be able to temporarily grant additional permissions. But this is quite dangerous, which is why the ideal (and far better) mechanism for temporarily elevating permissions is Module Signing.

For more details on why impersonation, as well as TRUSTWORTHY ON and Cross-DB Ownership Chaining, are bad, and how / why Module Signing is better, please see:

PLEASE, Please, please Stop Using Impersonation, TRUSTWORTHY, and Cross-DB Ownership Chaining


Update only changed columns with Mysql prepared statements

If you are concerned about issuing an UDPATE with lots of columns that don't happen to change, don't worry about it.

If you don't have all the possible "new values" and want to build a custom UPDATE every time, then do just that.

$sets = array();
$binds = array();

if (...) {
    $sets[] = "foo = :foo";
    $binds['foo'] = $foo_value;
}
if (...) {
    $sets[] = "bar = :bar";
    $binds['bar'] = $bar_value;
}
...

$sql = "UPDATE ... SET " .
       implode(', ', $sets) .
       " WHERE ...";
$sth = $pdo->prepare($sql);
$sth=>execute($binds);

Stored Procedure

When using CONCAT() to construct a query, be sure to use QUOTE() around any values that as suspect -- such as might have quotes in them, or might be subject to 'sql injection'.

See: String Functions in the MySQL 5.7 Reference Manual.


How to installation spark standalone mode in ubuntu

I'd recommend using pip pyspark, as it's super easy
$ python3 -m venv venv
$ source venv/bin/activate
$ pip install pyspark
$ pyspark

You can modify Spark configuration by adding files to venv/lib/python3.x/site-packages/pyspark/conf/


Behaviour of Bulk Insert in pgsql or any Database

Your statement runs in a transaction. If there is not already a transaction open, a transaction is created just for this statement and automatically committed at the end if the statement succeeds.

If the statement encounters an error, the transaction is rolled back automatically, so it has no effect.

Either the whole statement takes effect or none of it does.

(Note that you could've just tried it to determine this).


standard steps to Restart Seconday server in Logshipping setup

What I usually do with our 15-minute log shipping secondary servers is just wait until the last log is copied over and restored. Then you can reboot the secondary server (assuming that by server you mean a separate physical server). As soon as the secondary comes back up, it will copy over the missing transaction log file and restore them.

We restore our secondary in STANDBY, so some people query them once in a while, just make sure you let the appropriate people know that you're about to reboot the server.


To grant privilege to create a view:

GRANT CREATE VIEW TO STD01;

To grant the DML privileges:

GRANT SELECT,UPDATE,INSERT,DELETE ON STD00.CUSTOMER TO STD01;

But that's not all of the object privileges. If you did:

GRANT ALL ON STD00.CUSTOMER TO STD01;

you would also give other privileges such as ALTER, INDEX, FLASHBACK, etc.


The problem is that although the user was not listed in mysql.user table, there was an entry in mysql.db with that user.

So the solution is: check also mysql.db for colliding usernames.


The following did the trick. Laravel raw expressions.

DB::raw('GeomFromText(\'POLYGON('.$polycoords.')\')


Select Most Recently Updated Row Based on Sequence ID with Three Tables

Unless I missed something, the following will give you what is needed :

SELECT * FROM
(
select
  p.Person_ID,
  p.Person_Name,
  p.Person_Age,
  d.Department_Name,
  c.Company_Name,
  c.Company_City,
  c.Company_State,
  p.Person_Seq_ID,
  d.Department_ID,
  d.Department_Seq_ID,

  MAX(p.Person_Seq_ID) OVER(PARTITION BY p.Person_ID) as mPerson,
  MAX(d.Department_Seq_ID) OVER(PARTITION BY p.Person_ID, d.Department_ID) as mDep

from Person p
left join Department d
  on p.Person_Department_ID=d.Department_ID
left join (
  select Company_ID, Company_Name, Company_City, Company_State
  from Company
  group by Company_ID, Company_Name, Company_City, Company_State
) as c on c.Company_ID=d.Department_Company_ID
where c.Company_ID=1
  ) a
WHERE Person_Seq_ID = mPerson and Department_Seq_ID = mDep

Here is an updated link showing this on SQLFiddle.


Create a job that is scheduled to start every minute. Have the job do something like:

WHILE 1=1
BEGIN
    EXEC dbo.SomeProcedure; /*  this would be the
        name of a stored procedure that does the
        actual work */
    WAITFOR DELAY '00:00:01.000';
END

Thanks to Max Vernon, who gave me this answer earlier.


Community wiki answer:

  I'm really just wondering which might be the better configuration

On the low end workload spectrum, you probably won't notice a difference. On the higher workload spectrum I'd copy or come close to the underlying hardware setup.

Note that depending on the version of ESXi you use, having less than 8 vCPUs won't actually change the cores/node and you may have to edit the virtual machine config file to override the changes in default behaviour.

The output from the sys.dm_os_nodes query shows that VMWare overrode your config and gave you a single NUMA node.


How to set a value if certain columns are the same across multiple rows

Your test setup

(Best provided this way in your question - hint!)

CREATE TEMP TABLE tmp (
  curid int
, cuid  int
, rtid  int
, cd    date
, dd    date
, rm    text);

INSERT INTO tmp VALUES
  (10, 4, 4, '2016-01-02', '2016-07-02', NULL)
 ,(16, 4, 4, '2016-06-12', '2016-12-12', 'Remarks Jun 12')
 ,(18, 5, 3, '2016-07-18', '2017-07-31', NULL)
 ,(8 , 5, 3, '2015-06-21', '2016-06-30', 'Add some test')
 ,(11, 6, 4, '2017-01-01', '2017-07-01', NULL)
 ,(9 , 7, 3, '2017-01-01', '2018-01-31', NULL);

Solution

Create target tables if they don't exist:

CREATE TEMP TABLE a (
   id    serial
 , curid int  -- UNIQUE?
 , cuid  int
 , rtid  int
);

CREATE TEMP TABLE b (
   id    serial
 , curid int
 , cd    date
 , dd    date
 , rm    text
);

Use DISTINCT ON for table A:

INSERT INTO a (curid, cuid, rtid)
SELECT DISTINCT ON (cuid, rtid)
       curid, cuid, rtid
FROM   tmp
ORDER  BY  cuid, rtid, curid  -- pick smallest curid per group
RETURNING *;

id | curid | cuid | rtid
-: | ----: | ---: | ---:
 1 |    10 |    4 |    4
 2 |     8 |    5 |    3
 3 |    11 |    6 |    4
 4 |     9 |    7 |    3

Detailed explanation here:

Select first row in each GROUP BY group?

Use a simple window function for table B:

INSERT INTO b (curid, cd, dd, rm)
SELECT min(curid) OVER (PARTITION BY cuid, rtid), cd, dd, rm
FROM   tmp
ORDER  BY cuid, rtid  -- optional
RETURNING *;

id | curid | cd         | dd         | rm
-: | ----: | :--------- | :--------- | :-------------
 1 |    10 | 2016-01-02 | 2016-07-02 | null
 2 |    10 | 2016-06-12 | 2016-12-12 | Remarks Jun 12
 3 |     8 | 2016-07-18 | 2017-07-31 | null
 4 |     8 | 2015-06-21 | 2016-06-30 | Add some test
 5 |    11 | 2017-01-01 | 2017-07-01 | null
 6 |     9 | 2017-01-01 | 2018-01-31 | null

curid is guaranteed to match since we picked the smallest per group in both queries.

dbfiddle here


The recommendation from your Providers DBA is sufficient for your needs.  I would add the additional step of checking to make sure you don't have any heaps, however.  When you delete records from Heaps the space is not always made available and you won't have a good way of reorganizing it.  If you can, add a clustered index to the heap before deleting (or after, dealers choice).  If you cannot, I would delete, add a clustered index and then remove it.

SELECT T.name AS TableName
    , I.* FROM sys.indexes AS I
    INNER JOIN sys.tables AS T ON T.object_id = I.object_id
WHERE I.type_desc = 'HEAP'

So, my steps would be:

Check for Heaps and decide on course of action.
purge data (preferably in chunks), check-pointing or backing up the log as
applicable.
defragment indexes (Ola Hallengren's scripts will help
here if you don't have your own).  Do this first so that pages can be readjusted to the correct fill factor.  Otherwise, space used estimate will be off since a partially used page is counted as a full page and you will probably have lots of partially full pages that can be consolidated without impact.
Look at current database file size
used.  Shrink to appropriate size (I like to leave at least 20% empty
space in the file.
Defragment your indexes again since the shrink moved everything around.
Take the time to evaluate file growth settings
to make sure they make sense for your environment.  Usually growing
in fixed size rather than as a percentage and make sure you have
instant file initialization turned on.

This will tell you how much space is actually being used in the file.

SELECT DB_NAME() AS DatabaseName
, F.name
, F.type_desc
, F.state_desc
, F.file_id
, F.growth
, F.max_size
, F.is_percent_growth
, F.physical_name
, F.size/128.0 AS CurrentSize_MB
, FILEPROPERTY(F.name, 'SpaceUsed')/128.0 AS SpaceUsed_MB
 FROM sys.database_files F


How to "group" based on consecutive occurrences of an element in a column in postgres

I personally think this is not a good design because all intermediate stop points must be included into two segments.

IMHO, you should be able to transform it into something similar to:

id | start | end | stop
---|-------|-----|-----
 1 |   1   |  2  |
 2 |   2   |  3  | yes
 3 |   3   |  4  |
 4 |   4   |  5  |
 5 |   5   |  6  | yes

But using your current schema you could try by using:

WITH x AS
(
  SELECT
      point,
      stop,
      LEAD(point) OVER (ORDER BY point) nxt,
      SUM(CASE WHEN stop = true THEN 1 ELSE 0 END) OVER (ORDER BY point) grp
  FROM
      tbl
  ORDER BY
      point
)
SELECT
    grp, ST_MakeLine(array_agg(ST_MakePoint(point, nxt::int))) lines
FROM
    x
WHERE
    nxt IS NOT NULL
GROUP BY
    grp
ORDER BY
    grp;

That basically returns an array of points like this:

grp | points
--: | :------------------------
  1 | {"(1,2)","(2,3)"}
  2 | {"(3,4)","(4,5)","(5,6)"}

Then using ST_MakePoint and ST_MakeLine you can get:

grp | lines
--: | :-----------------------------------------------------------------------------------------------------------------
  1 | 010200000002000000000000000000F03F000000000000004000000000000000400000000000000840
  2 | 010200000003000000000000000000084000000000000010400000000000001040000000000000144000000000000014400000000000001840

db<>fiddle here


Can I make a full export of a corrupted database?

By default the full scan on that table with a corrupt block will fail, but you can make the scans skip the corrupt blocks of an object:

DBMS_REPAIR.SKIP_CORRUPT_BLOCKS

  This procedure enables or disables the skipping of corrupt blocks
  during index and table scans of the specified object.

DBMS_REPAIR.SKIP_CORRUPT_BLOCKS (
   schema_name  IN VARCHAR2,
   object_name  IN VARCHAR2,
   object_type  IN BINARY_INTEGER DEFAULT TABLE_OBJECT,
   flags        IN BINARY_INTEGER DEFAULT SKIP_FLAG);

In RMAN you can create a backup with corrupt blocks by specifying the allowed maximum amount of corrupt blocks:

SET

  MAXCORRUPT FOR DATAFILE datafileSpec TO integer

  Sets a limit on the number of previously undetected block corruptions
  that the database permits in a specified data file or group of data
  files. The default limit is zero, meaning that RMAN tolerates no
  corrupt blocks.

  The SET MAXCORRUPT command specifies the total number of physical and
  logical corruptions permitted in a data file during a backup job. If
  the sum of physical and logical corruptions detected for a data file
  is no more than its MAXCORRUPT setting, then the BACKUP command
  completes. If more than MAXCORRUPT corrupt blocks exist, then RMAN
  terminates without creating output files.

But you should just fix the object with the corrupt block instead of ignoring it.


The easiest way is to hold reference to itself in the duplicate_with for the master records while their is_duplicate fields contains zero. Then all the group (master and all its slaves) can be extracted by duplicate_with field value:

SELECT *
  FROM sample
 ORDER BY duplicate_with ASC
        , is_duplicate DESC
        , id ASC
;

If you can't for some reason update master records then you can do the same by joining table with itself:

SELECT z.id AS id
     , z.is_duplicate AS is_duplicate
     , z.duplicate_with AS duplicate_with
     , z.name AS name
  FROM sample AS w
  JOIN sample AS z ON z.duplicate_with = w.id
                   OR z.id = w.id
 ORDER BY w.id ASC
        , z.is_duplicate DESC
        , z.id ASC
;

Sure that approach can be significantly slower on the big tables.


You are confusing Server Network Protocols and Client Network Protocols. Essentially, Server Network Protocols change the way in which SQL Server listens for incoming requests and Client Network Protocols change the way in which an application sends a request to SQL Server.

As the Client Network Protocols are a shared component and apply to all applications that use that library you cannot set a different protocol per instance that you wish to connect to.

Direct from the Client Network Protocols link below:

  At the simplest level, a SQL Server client can reside on the same machine as an instance of SQL Server. Typically, however, a client connects to one or more remote servers over a network. The client/server architecture of SQL Server allows it to seamlessly manage multiple clients and servers on a network. The default client configurations suffice in most situations.

More information regarding the two can be found in the links below:

Server Network Protocols

Client Network Protocols


Automating the process is simple once you know what you want to delete.

I can give you a few hints:

Typically, you want to remove all WAL archives older than the oldest base backup which you want to retain.
Whenever a base backup is done, PostgreSQL will archive a file called *.backup.

You can use this file as the second argument to pg_archivecleanup to remove everything older than that backup.

So to retain the last three backups, you could look at all *.backup files, sort them by modification time and find the third youngest file, then use pg_archivecleanup with that file as second argument.


Using sys.dm_db_index_usage_stats for unhelpful or unused indexes

Just throwing this out there, because I remember the bad old days of having a different script for every index ailment you read about on the internet.

I co-author a free stored procedure called sp_BlitzIndex that will tell you about a whole bunch of stuff going on with your indexes all at once.

Some examples:

Aggressively locked indexes
Duplicates (based on keys)
Borderline duplicates (based on first key column)
Unused (with differentiation based on number of writes
High value missing indexes
HEAPs
And more!

The simplest example run is probably like this. Just change the database name.

EXEC sp_BlitzIndex @DatabaseName = 'StackOverflow', @Mode = 4

There are a ton more ways to run it, just check out the docs at the GitHub link.

Hope this helps!


Distinct in one column where another column has same values

SELECT name, MIN(type) AS type
FROM table_name
GROUP BY name
HAVING COUNT(DISTINCT type) = 1 ;

or the same with:

HAVING MIN(type) = MAX(type)

I assume that type is not nullable.

If you want only those that have type = 'a', use:

HAVING MIN(type) = 'a'
   AND MAX(type) = 'a' ;


What is the usage of performance_schema in MySQL 5.5?

All of your answers can be found by reading the documentation:

What is the usage of this new schema..?

  The Performance Schema monitors server events. An “event” is anything the server does that takes time and has been instrumented so that timing information can be collected. In general, an event could be a function call, a wait for the operating system, a stage of an SQL statement execution such as parsing or sorting, or an entire statement or group of statements.

What are the advantages of using this schema..?

  The Performance Schema is a tool to help a DBA do performance tuning by taking real measurements instead of “wild guesses.” source

Is there any overhead by enabling of this schema..?

  Server monitoring occurs continuously and unobtrusively with very little overhead. Activating the Performance Schema does not make the server unusable.

How can we monitor using this schema..?

This page gives a basic overview of how to use the performance schema


Check for existing matches to find the field their grouped-by

It's a mess but I think it works:

http://sqlfiddle.com/#!9/585394/2

SELECT SessionID as ExistingMatch FROM (

    SELECT COUNT(c.ainc) as intcodematch,SUM(c.bnos) as Incorrect, c.SessionID FROM
      (
        SELECT a.SessionID,a.InterventionCode as ainc, b.InterventionCode as binc, IF(b.InterventionCode IS NULL,1,0) as bnos
            FROM sessionitems a
            left JOIN useritems b
              ON  a.`InterventionCode` = b.`InterventionCode` AND  a.`Included` = b.`Included` AND  a.`IntLvl` = b.`IntLvl` AND  a.`Uptake Baseline Smokers` = b.`Uptake Baseline Smokers` AND  a.`RelativeEffect` = b.`RelativeEffect` AND  a.`Cost` = b.`Cost` AND  a.`IntType` = b.`IntType` AND  a.`Baseline` = b.`Baseline` AND  a.`Current` = b.`Current` AND  a.`Alternative` = b.`Alternative` AND  a.`UpPopulation` = b.`UpPopulation` AND  a.`EffPopulation` = b.`EffPopulation`
        GROUP BY a.SessionID,a.InterventionCode

     ) c GROUP BY c.SessionID

) q WHERE q.Incorrect = 0 AND q.intcodematch = (SELECT COUNT(InterventionCode) FROM useritems)

I think I need to add support for nulls in Cost which is a possible situation or cast them to a '0'.

Any thoughts?


Cannot find slow (frozen) running query in Query Store

Not a full answer to all your questions but:

  What reasons makes the query stop from increasing CPU time?

As well as heavy IO (though I would expect CPU time should slowly tick up in that case too), there is the possibility that it is sat idle waiting for locks to be released by one or more other transactions. Check the relevant output of sp_who2 (built in but undocumented) or sp_whoisactive.

  I have heard about trace flags and extended events, but I do not have any experience with this.

Trace flags are not something to play with without a specific and well understood need. Extended events will be useful for tracking plan execution amongst many other things, but a full run-down is almost certainly out of the scope of a DBA.SE answer. There are plenty of examples out there, and presumably official documentation (though that tends to be optimised for reference, not initial learning).


OK, first things first, talk to the PHP devs to see if this situation is taken care of for you, which would then save a lot of pain and heartache!

Other than that, try with a group by instead of a distinct, see what the performance differences are - it may be that the group by is faster based on the indexes you have on the tables.

With the GROUP BY option, you can add a count(*) then talk to the php devs to ensure they know that if that result is 2 or more, then they need to handle it differently than if the result is a 1.


I think that using the team ID is the right way to go.  Another level of abstraction for all of the finals rounds just adds unnecessary complexity for not much benefit other than pre-loading the matches table with data.

The data structure looks pretty solid to support this.  The quarter and semi finals would need to be added to the matches table once the initial match results are in.  If the matches are assigned randomly then this is a manual operation, however, if they are in a particular order...

   A
match 1 -----+
   B         A
          match 5 -----+
   C         C         |
match 2 -----+         |
   D                   A
                    match 7
   E                   F
match 3 -----+         |
   F         F         |
          match 6 -----+
   G         G
match 4 -----+
   H

...then this could possibly be done with a query.  Again, the complexity of the query may not be worth the effort depending on the number of teams


Tell SQL Server a query is ad hoc and not to optimize based on it

You can add the query hint OPTION (RECOMPILE).

This effectively tells SQL Server, "Give me a plan to execute this query one time, but don't save it in the plan cache".

Take a peek at Kendra Little's article on RECOMPILE hints and execution plan caching.  She covers all the uses and potential effects.

RECOMPILE Hints and Execution Plan Caching

Also see:

Parameter Sniffing, Embedding, and the RECOMPILE Options

Regarding disabling the missing index feature, the only supported way to do that is to start SQL Server from the command line with the -x switch. That is not what you want, but it does set a limit on your options.


I've come to the conclusion that what i wan't is not possible in MDX. But it don't need to be. I also realized MDX is not SQL. SQL returns a dataset. MDX returns a dataset and metadata. Combine the two and I get what I wanted.

Pulling up my original query into an Excel PivotTable it was exactly what I was after. So it came down to SSMS limitations at displying MDX queries, and my own lack of understanding what MDX queries actually return. And what one really needs is some kind of cube analysis client to really benefit from MDX.

So the result of an MDX query is much more then just the dataset it returns but also all the properties you specify to get with it as metadata.


Is there any way to recover/rebuild the logs or any other procedure to make it work again? We do not have backups.

That depends on how you define "work again" as the log holds information to make the database consistent. This means even if you did get the database back up and running there is no guarantee that the data in it is correct. So maybe that "works" for you but if it's something like accounts payable, that might not be a solution.

You can give this a shot for user databases, but again YMMV and the data shouldn't be trusted. If there is a way to re-create the database and import the data, that would be best.

With system databases the easiest method is to rebuild them from the media. Since master is an issue in this instance, you'll lose information such as logins (if they are SQL logins their associated passwords), linked servers, any instance wide certificates, etc.

If you get this back up and running, I'd implement backups and lock down servers and permissions.


Anything like a hash for Postgresql databases?

It sounds like you might be looking for pg_comparator.

I haven't used it myself, but I've got some database check/compare work to do soon, and it's one of the options I need to look into as a starting point.


Slow connect time to postgresql on Windows 10

If PostgreSQL listen on localhost only, ensure IPv4 and IPv6 both addresses are set in postgresql.conf.

listen_addresses = '127.0.0.1,::1'.


You don't need single column indexes on fields that are also the first column in multi column indexes.

However you shouldn't specify the column order just based on that. For best performance, you want the column that has the highest cardinality (ie "uniqueness") first in the column order.

I think that you should just get rid of the CREATED_AT single column index and keep the other single column ones.

The 3 double column indexes should all  have the CREATED_AT field first ordered DESC for maximum efficiency.


Redo logs can be mirrored, your query does not take into account that. Controlfiles are also part of the database.

select 'Datafiles', sum(bytes)/1024/1024 from dba_data_files union all
select 'Tempfiles', sum(bytes)/1024/1024 from dba_temp_files union all
select 'Redo logs', sum(bytes)/1024/1024 from v$log l join v$logfile lf on (l.group# = lf.group#) union all
select 'Controlfiles', sum(block_size * file_size_blks)/1024/1024 from v$controlfile;

There are other files that are not critical, but they need space, for example standby redo logs and the block change tracking file:

select 'Datafiles', sum(bytes)/1024/1024 from dba_data_files union all
select 'Tempfiles', sum(bytes)/1024/1024 from dba_temp_files union all
select 'Redo logs', sum(bytes)/1024/1024 from v$log l join v$logfile lf on (l.group# = lf.group#) union all
select 'Controlfiles', sum(block_size * file_size_blks)/1024/1024 from v$controlfile union all
select 'Standby logs', sum(bytes)/1024/1024 from v$standby_log l join v$logfile lf on (l.group# = lf.group#) union all
select 'Change tracking', bytes/1024/1024 from v$block_change_tracking;


How can I remove only consecutive duplicate fields when the id is not consecutive in MySQL

This should do the job :

SELECT
  ph1.sku,
  ph1.id as ph1Id,
  ph2.id as ph2Id,
  ph1.histDate,
  ph2.amount as oldAmount,
  ph1.amount as newAmount
FROM priceHistory as ph1
LEFT JOIN priceHistory as ph2 ON
ph2.id =
(SELECT max(id) FROM priceHistory AS ph3 WHERE ph3.id < ph1.id and ph1.SKU = ph3.SKU)
WHERE ph1.amount <> ph2.amount OR
      ph2.amount is null

Here is the fiddle.


Execute a script after transactional replication setup completes

I need assistance to execute/run automatically below .SQL file/Query
  automatically at subscriber once publication is done

You should use sp_addscriptexec tsql to run sql script on all subscribers of a publication once the snapshot is applied to subscribers. Make sure you use @skiperror = 0 to stop the distribution agent when an error is encountered.

Brandon Williams has a good blog post on - Executing scripts with sp_addscriptexec


How to verify an oracle database license

Anybody can download and install oracle software without any installation codes being necessary. So in effect an Oracle licence is simply an entry in their Licence Management Services (LMS) database. Depending upon what you buy you will get a CSI number. In working with Oracle for more than 15 years this is all I have ever seen at customer sites: a piece of paper with a description of the licence(s), the total number bought and a CSI number. One way to verify your licence would be to create an account on their support site (support.oracle.com) and add your CSI to this account. Then when you log in you can see which products are associated with this CSI. An alternative would be to do as Threaten suggests and simply email LMS and they will send you a list of what they think you own. For the amount of money you pay for an 11gDB licence I would expect something more substantial but unfortunately a sheet of plain text is all you get.


Memory Limit Exceeded

The optimizer was forced to stop looking for better plan alternatives due to memory pressure. The reason for that should be investigated and corrected, then query compilation attempted again. The plan returned may very well not be the one the optimizer would have selected had the low memory condition not existed.
Time Out

This reason is much misunderstood.

The query optimizer aims to find a reasonable plan quickly. It does not perform an exhaustive search to find the best possible plan. By design, it avoids spending more time on optimization than necessary. One of these features that works to ensure this is the 'time out' (not a measure of time).

The optimizer sets itself an 'exploration budget' based on the complexity of the logical query, cardinality estimates, and the estimated cost of the cheapest plan found so far (if any). More complex queries with higher cardinality are given a higher budget.

If this budget is exceeded during one of its search phases, the phase ends. This is part of the design and normal operation of the optimizer. Queries that need more optimizer effort get it; ones that don't, don't.

Think of 'Time Out' as 'Good Enough Plan Found'.
Good Enough Plan Found

This means exactly the same as a blank reason. It is simply a historical quirk that plans with a cost below 0.909090... (1/1.1) are labelled this way. Nothing stops early or is otherwise specially handled or different within the optimizer code when this reason appears.

Aside from Memory Limit Exceeded, none of the 'early termination reasons' mean much (if anything at all) for query tuning or performance analysis. I generally ignore them.

Advice

Target query tuning efforts based on real performance metrics (elapsed time, CPU/memory usage, ... whatever is important in context). If a query is too slow for its intended purpose, spend time making it faster. Measure actual performance, compare it with the baseline and history, and target tuning effort at the important variances.

Store guaranteed-clean data in a proper relational schema, with useful statistics and indexes, and well-written, optimizer-friendly queries.


What is the easiest way to install the latest postgis_tiger_geocoder?

Well, they don't make this easy because it's part of core. It's also not fun that this is hard coded into the extension. You can either

Follow the instructions in the official FAQ by extracting from the .zip
Rebuild the entirety of PostGIS which is a monstrously fun task -- though it is totally doable. There is no easy way to build just the extension. The extensions build after the documentation and the rest of PostGIS. Good luck and god speed.
Download PostGIS-Scripts from the PDPG apt archive. Note, the file you want is labeled postgresql-$majorversionpg-postgis-$majorversionpostgis-scripts_$fullversionpostgis with the version of PostgreSQL and PostGIS. Do not download the transition packages that lack the $majorversion portion of the name.

After you download extract the files with dpkg -X justDownloadedArchive.deb ./out
Copy out/*geocoder* to your extension folder (/usr/share/postgresql/*/extension/) where * is some version number.


sp_BlitzIndex "Missing Index" section has columns but no values/results

That just means SQL Server's not recommending indexes for that table, or the index recommendations aren't a significant benefit. We filter out trivially insignificant missing indexes, like those with a very low benefit that have only been run a few times.

If you'd like to try other scripts, you can use any number of them.


Setting up SQL server multiple Names but within the same Database

Yes you can use a CNAME record in DNS
there are a few gotchas though so worth reading through the article
https://www.mssqltips.com/sqlservertip/2663/using-friendly-names-for-sql-servers-via-dns/
if you only need to "rename " the server from a single client there are other alternatives


What is the use of "Backup set will Expire:" option while creating Backup Database task in Maintenance plan

The "Backup set will Expire" option is used to indicate how long a backup should be prohibited from being over written. It will not clean up old backup files. You can use the "Maintenance Clean up Task" in your maintenance plan to clean up backup files older than a certain number of days.


Best way to optimize an aggregated issues over time query?

Save the aggregation in a "summary table".  Then add a new row(s) each night.  The 'report' would sum the sums and sum the counts.  (Average would be (sum of sums) / (sum of counts).)
I'm confused.  You seem to have a date in the Fact table, yet you want to summarize for one day?  Or a range of days?  Please provide SHOW CREATE TABLE and say approximately how big the tables are.
More discussion:  http://mysql.rjweb.org/doc.php/summarytables


Why is SELECT USER_NAME() returning my windows credentials and not the database user mapped to a windows security group that I am in?

If a Windows principal accessed the database by way of membership in a group, CURRENT_USER will return the name of the Windows principal instead of the group name.
https://docs.microsoft.com/en-us/sql/t-sql/functions/current-user-transact-sql?view=sql-server-ver15


CheckDB on Partitioned Table - Insufficient Memory in resource Pool default

Paul's explanation might answer your question , you can take a look here
http://www.sqlskills.com/blogs/paul/checkdb-from-every-angle-why-would-checkdb-run-out-of-space/. I have copy pasted a snippet from his post related to your question:

There’s another reason that DBCC CHECKDB may stop with an out-of-space error.  DBCC CHECKDB has to validate the contents of the whole database. As such it needs to store info about things it has seen at one point during database scanning so that it can match that info against things it sees at later points during database scanning. These bits of info are stored in an in-memory worktable. In many cases though, the amount of info being stored exceeds the available memory and the worktable needs to spill over to tempdb. (For example, running DBCC CHECKDB against a 1TB database on a machine with 16GB of memory – the amount of info that DBCC CHECKDB needs to store the intermediate pieces of info will likely exceed the memory available to SQL Server). So – if tempdb isn’t big enough to store the worktable, it has to grow. If it can’t grow, then DBCC CHECKDB will fail because it needs the worktable to operate.


PostgreSQL 11: Replace function body by users from the same group

Consider this example setup:

CREATE ROLE function_creator;

CREATE USER radu;
CREATE USER ion;

GRANT function_creator TO radu, ion; -- this is 'being a member of the owning role'

GRANT ALL ON SCHEMA here_live_the_functions TO function_creator;

Then, when you create functions:

SET ROLE TO function_creator;

CREATE OR REPLACE FUNCTION ...;

After all this, you and ion will be able to change (redefine) the function, which is owned by function_creator.

If you need the debug user, you can add one more level:

CREATE ROLE debugger;

GRANT function_creator TO debugger;

-- and instead of granting function_creator to the actual users, do
GRANT debugger TO radu, ion;


MDF, LDF, TempDb Location on same Hybrid SAN

The basic principle do not change regardless of what SAN, DAS or USB sticks you use to hold the data and transaction log files. TempDB is just a "special" database where its data and transaction log files have different workload behavior considerations since it can serve 1 - '000s of databases.

You design your database storage to handle the IOPS, throughput (MB/s), latency and reliability/availability. Modern SAN solutions largely removed the need to have custom designed physical drives and file placements. There's no magic and it's not a free ride. The SAN has to be setup to deliver on the numbers and ensuring it has the right level of protection against failure, including perf impact during partial failures. It's really boils down to letting the vendors/admins configure their physical layout optimized for their SAN and delivering on the key requirements listed above.

If you can correctly measure what you need and the SAN admins signup to deliver on the SLA, I won't try to get into the physical layout. For starters, it's technically interesting but you're signing up for long term maintenance. Also, with most SANs, you really shouldn't or even cannot pick some arbitrary physical disk configuration. Critical point here is having the right numbers for the 4 areas above and the SAN admins signing off and being accountable to deliver.


Monitor delete statements on a specific table on DB2 LUW

Use the AUDIT TABLE statement on this table using an AUDIT POLICY with the EXECUTE category.
Or create an AFTER UPDATE trigger.


Best way to handle dates prior to 1000 A.D. in MySQL?

No native RDBMS date data type is going to do for applications that require very old (and for some, even distant future) dates.

If I were you, I'd use a string type for the native storage and stick with a place-significant format like: +YYYY-MM-DD to accomodate BC/AD and any foreseeable historical or reasonable future date.

If it might help, you could build a library class that converts your internal storage format into a more presentable one for the UI layer.  You might even include library functions that convert to a native date type, if your language of choice supports the dates that you will have in your database.


I think it's safe to say the or @parameters IS NOT NULL in SQL Server 2012 is a bug.

SQL Server 2012 v11.0.6260.1 also has the erroneous code.
SQL Server 2016 v13.0.4446.0 does not have that piece.
SQL Server 2017 v14.0.3025.34 does not have that piece.

Furthermore, the code prevents sp_syscollector_validate_xml from being called for non-system collection sets.

Since the stored procedure appears to be a system-stored-procedure, you'll likely need to use sp_MS_marksystemobject to mark the stored procedure as a system-stored-procedure after you modify it to remove the or @parameters IS NOT NULL code.

Be aware that changing procedures that have been shipped by Microsoft may make support more difficult.  I would recommend against making this change on production-critical instances.

For reference, the code as seen in my SQL Server 2012 instance is:

CREATE PROCEDURE [dbo].[sp_syscollector_update_collection_item]
    @collection_item_id        int = NULL,
    @name                    sysname = NULL,
    @new_name                sysname = NULL,
    @frequency                int = NULL,
    @parameters                xml = NULL
AS
BEGIN
    -- Security check (role membership)
    IF (NOT (ISNULL(IS_MEMBER(N'dc_operator'), 0) = 1) AND NOT (ISNULL(IS_MEMBER(N'db_owner'), 0) = 1))
    BEGIN
        RAISERROR(14677, -1, -1, 'dc_operator')
        RETURN(1) -- Failure
    END

    -- Security checks (restrict functionality for non-dc_admin-s)
    IF ((NOT (ISNULL(IS_MEMBER(N'dc_admin'), 0) = 1) AND NOT (ISNULL(IS_MEMBER(N'db_owner'), 0) = 1))
        AND (@new_name IS NOT NULL))
    BEGIN
        RAISERROR(14676, -1, -1, '@new_name', 'dc_admin')
        RETURN (1) -- Failure
    END
    IF ((NOT (ISNULL(IS_MEMBER(N'dc_admin'), 0) = 1) AND NOT (ISNULL(IS_MEMBER(N'db_owner'), 0) = 1))
        AND (@parameters IS NOT NULL))
    BEGIN
        RAISERROR(14676, -1, -1, '@parameters', 'dc_admin')
        RETURN (1) -- Failure
    END

    DECLARE @retVal int
    EXEC @retVal = dbo.sp_syscollector_verify_collection_item @collection_item_id OUTPUT, @name OUTPUT
    IF (@retVal <> 0)
        RETURN (@retVal)

    IF (@frequency < 5)
    BEGIN
        DECLARE @frequency_as_char VARCHAR(36)
        SELECT @frequency_as_char = CONVERT(VARCHAR(36), @frequency)
        RAISERROR(21405, 16, -1, @frequency_as_char, '@frequency', 5)
        RETURN (1)
    END

    IF (LEN(@new_name) = 0)  -- can't rename to an empty string
    BEGIN
      RAISERROR(21263, -1, -1, '@new_name')
      RETURN(1) -- Failure
    END

    -- Remove any leading/trailing spaces from parameters
    SET @new_name            = LTRIM(RTRIM(@new_name))

    DECLARE @collection_set_name sysname
    DECLARE @is_system              bit
    DECLARE @is_running             bit
    DECLARE @collector_type_uid     uniqueidentifier
    DECLARE @collection_set_id      int
    SELECT @is_running = s.is_running,
           @is_system = s.is_system,
           @collection_set_name = s.name,
           @collector_type_uid = i.collector_type_uid,
           @collection_set_id = s.collection_set_id
    FROM dbo.syscollector_collection_sets s,
         dbo.syscollector_collection_items i
    WHERE s.collection_set_id = i.collection_set_id
    AND i.collection_item_id = @collection_item_id

    IF (@is_system = 1 AND (@new_name IS NOT NULL OR @parameters IS NOT NULL))
    BEGIN
        -- cannot update, delete, or add new collection items to a system collection set
        RAISERROR(14696, -1, -1);
        RETURN (1)
    END

    IF (@parameters IS NOT NULL)
    BEGIN
        EXEC @retVal = dbo.sp_syscollector_validate_xml @collector_type_uid = @collector_type_uid, @parameters = @parameters
        IF (@retVal <> 0)
            RETURN (@retVal)
    END

    -- if the collection item is running, stop it before update
    IF (@is_running = 1)
    BEGIN
        EXEC @retVal = sp_syscollector_stop_collection_set @collection_set_id = @collection_set_id
        IF (@retVal <> 0)
            RETURN(1)
    END

    -- all conditions go, perform the update
    EXEC @retVal = sp_syscollector_update_collection_item_internal
                            @collection_item_id = @collection_item_id,
                            @name = @name,
                            @new_name = @new_name,
                            @frequency = @frequency,
                            @parameters = @parameters

    -- if you stopped the collection set, restart it
    IF (@is_running = 1)
    BEGIN
        EXEC @retVal = sp_syscollector_start_collection_set @collection_set_id = @collection_set_id
        IF (@retVal <> 0)
            RETURN (1)
    END

    RETURN (0)
END

The code in SQL Server 2016 is:

CREATE PROCEDURE [dbo].[sp_syscollector_update_collection_item]
    @collection_item_id        int = NULL,
    @name                    sysname = NULL,
    @new_name                sysname = NULL,
    @frequency                int = NULL,
    @parameters                xml = NULL
AS
BEGIN
    -- Security check (role membership)
    IF (NOT (ISNULL(IS_MEMBER(N'dc_operator'), 0) = 1) AND NOT (ISNULL(IS_MEMBER(N'db_owner'), 0) = 1))
    BEGIN
        RAISERROR(14677, -1, -1, 'dc_operator')
        RETURN(1) -- Failure
    END

    -- Security checks (restrict functionality for non-dc_admin-s)
    IF ((NOT (ISNULL(IS_MEMBER(N'dc_admin'), 0) = 1) AND NOT (ISNULL(IS_MEMBER(N'db_owner'), 0) = 1))
        AND (@new_name IS NOT NULL))
    BEGIN
        RAISERROR(14676, -1, -1, '@new_name', 'dc_admin')
        RETURN (1) -- Failure
    END
    IF ((NOT (ISNULL(IS_MEMBER(N'dc_admin'), 0) = 1) AND NOT (ISNULL(IS_MEMBER(N'db_owner'), 0) = 1))
        AND (@parameters IS NOT NULL))
    BEGIN
        RAISERROR(14676, -1, -1, '@parameters', 'dc_admin')
        RETURN (1) -- Failure
    END

    DECLARE @retVal int
    EXEC @retVal = dbo.sp_syscollector_verify_collection_item @collection_item_id OUTPUT, @name OUTPUT
    IF (@retVal <> 0)
        RETURN (@retVal)

    IF (@frequency < 5)
    BEGIN
        DECLARE @frequency_as_char VARCHAR(36)
        SELECT @frequency_as_char = CONVERT(VARCHAR(36), @frequency)
        RAISERROR(21405, 16, -1, @frequency_as_char, '@frequency', 5)
        RETURN (1)
    END

    IF (LEN(@new_name) = 0)  -- can't rename to an empty string
    BEGIN
        RAISERROR(21263, -1, -1, '@new_name')
        RETURN(1) -- Failure
    END

    -- Remove any leading/trailing spaces from parameters
    SET @new_name            = LTRIM(RTRIM(@new_name))

    DECLARE @collection_set_name sysname
    DECLARE @is_system              bit
    DECLARE @is_running             bit
    DECLARE @collector_type_uid     uniqueidentifier
    DECLARE @collection_set_id      int
    SELECT @is_running = s.is_running,
            @is_system = s.is_system,
            @collection_set_name = s.name,
            @collector_type_uid = i.collector_type_uid,
            @collection_set_id = s.collection_set_id
    FROM dbo.syscollector_collection_sets s,
            dbo.syscollector_collection_items i
    WHERE s.collection_set_id = i.collection_set_id
    AND i.collection_item_id = @collection_item_id

    IF (@is_system = 1 AND (@new_name IS NOT NULL))
    BEGIN
        -- cannot update, delete, or add new collection items to a system collection set
        RAISERROR(14696, -1, -1);
        RETURN (1)
    END

    IF (@parameters IS NOT NULL)
    BEGIN
        EXEC @retVal = dbo.sp_syscollector_validate_xml @collector_type_uid = @collector_type_uid, @parameters = @parameters
        IF (@retVal <> 0)
            RETURN (@retVal)
    END

    -- if the collection item is running, stop it before update
    IF (@is_running = 1)
    BEGIN
        EXEC @retVal = sp_syscollector_stop_collection_set @collection_set_id = @collection_set_id
        IF (@retVal <> 0)
            RETURN(1)
    END

    -- all conditions go, perform the update
    EXEC @retVal = sp_syscollector_update_collection_item_internal
                            @collection_item_id = @collection_item_id,
                            @name = @name,
                            @new_name = @new_name,
                            @frequency = @frequency,
                            @parameters = @parameters

    -- if you stopped the collection set, restart it
    IF (@is_running = 1)
    BEGIN
        EXEC @retVal = sp_syscollector_start_collection_set @collection_set_id = @collection_set_id
        IF (@retVal <> 0)
            RETURN (1)
    END

    RETURN (0)
END


Oracle SQL Developer: 'All Source Lines' in 'Find Database Object'

I'm going to answer your question based on version 18.2.

Searching source code in an Oracle Database via SQL Developer:

We automatically wildcard your search string - so you can search db object names, like tables named 'EMP' and also look for that string in your database code. We also make sure the case of the text isn't an issue.

Now, what do we consider 'source code?'

All Source Lines: this is the ALL_SOURCE view, in other words, the PL/SQL code you have access to
All Views - the SQL contained in the definition of your View and Materialized Views
All Statements - new for 12cR2 databases, this is the SQL statements collected by PL/Scope via the pl/sql compiler for your pl/sql code
ORDS - the code used to define your RESTful Services

I talk about the search feature here and provide a video demo.


How to use 'Bind Variables' in a 'Dynamic Query ' when the exact number of variables are not known

split the variable and fetch each element. That is, use a cursor


Here's another alternative which admittedly has an additional cost of ~0.005 but I find much easier to follow.

;WITH i AS
(
  SELECT Customer, dolamt = SUM(dolamt)
  FROM @info GROUP BY Customer
),
w AS
(
  SELECT Customer, woID, companycost,
    rn = ROW_NUMBER() OVER (PARTITION BY Customer ORDER BY woID)
  FROM @WorkOrders
)
SELECT c.Customer,
  [Work Order ID] = COALESCE(w.woID, 0),
  [Company Cost]  = COALESCE(w.companycost,0),
  [Total Amt] = COALESCE(CASE w.rn WHEN 1 THEN i.dolamt END,0)
FROM @Custs AS c
LEFT OUTER JOIN w ON c.Customer = w.Customer
LEFT OUTER JOIN i ON c.Customer = i.Customer
ORDER BY c.Customer;


How do pages get updated checksum when switching page verification mode from torn page to checksum?

It won’t happen all at once. Each page will get changed independently when it is modified through some other action (e.g. insert or update).
This was done precisely so that this change would not be a size-of-data operation.
Of course you could force it to be immediate for a given table by rebuilding, but the big cost there would be the rebuild, since you’re writing all of the page contents anyway. Switching those pages to checksum as a part of the process is an almost-zero-cost add-on, like how much gas mileage you lose if you add a kid to the carpool.


Does Express Profiler have a major effect on performance of SQL Server Express

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


Slony Replication: Will data eventually be 100% replicated if daemon dies for long period?

This is a fairly expert-level question and I could be wrong, but I think you are going to have a lot of issues in the scenario you suggest.

The basic issue is that while Slony is designed to handle periods of inactivity, you have a lot of stuff that is going to be queued for replication.  If you make any DDL queries in the mean time, those can't get queued, and slonyk will not work to replicate them, so you will have a significant number of problems here.

My recommendation is not to use Slony for things where you expect long periods of inactivity (say, more than a couple of days).  The limitations you will run into keeping this running are significant.

So yes, your data will eventually get replicated, maybe....  It depends on whether other problems surface in the mean time.  I wouldn't recommend it in that case.


Trying to connect two SQL Server databases

Looks like name resolution is not working. Do you have a DNS server? If not, you may have to make entries in the hosts files on both computers to enable name resolution. The hosts file on Windows is at c:\windows\system32\drivers\etc\hosts.
Try using FQDN. If the server is in a different domain, and a host record is not created in DNS, this would be required to resolve. You could also try creating an alias in SQL Server Configuration Manager.


MongoDB Indexing - What happens if I don't index all necessary fields?

Let's say I have a collection with 20 fields and a query that uses 5 of them, to find the documents I need.

  3/5 of those fields are always used in the query, 1 for equality comparison, 1 for range comparison (less than) and the other for sorting. The other 2 are being used conditionally, so sometimes they will and others they won't.

  Those 2, should I index them?

If you have an index on three fields supporting your common equality/sort/range queries, this index is still a viable candidate for a query including additional fields. There will be some performance cost for fetching documents to find values that aren't included in the index, but this may be acceptable depending on your queries and data distribution.

The details of your common queries will also influence whether an index on five fields can effectively support lookups on three. If your common queries include different combinations of equality, range, and sort fields you will likely need multiple indexes.

One situation to be wary of is having multiple candidate indexes that might support a given query shape, but no ideal index. For example: if you have an index on the three common fields and a separate index including the two less common fields, the outcome of planning for a query involving all five fields may not be deterministic.

  If I don't, will I still benefit from indexing the first 3?

If you don't create an index on any of the fields used in a query, the only possible query plan will be a collection scan (i.e. read all documents in the collection to find matching values). That's generally a situation to avoid unless your query is likely to fetch most of a collection. An index on a subset of fields is likely to be more useful than no index.

The overall benefits can only be qualified by comparing the execution stats given your actual queries and representative data. An index on all fields might be more efficient if the additional fields are selective and there would be relatively few index lookups compared to the number of documents to scan. However, additional fields will also increase the size of the index data which might be detrimental if these fields aren't frequently queried.

For a better comparison I would try creating both indexes in a test environment with some representative data and then review the explain results for candidate plans for your common queries:

db.mycollection.find(...).explain('allPlansExecution')

If you have many candidate indexes you may find it easier to use an index hint to force a specific index to be selected in your test environment. For production environments you should only add required indexes.

Another consideration would be matching indexes to actual usage in your deployment. You could add indexes based on slow or inefficient queries as observed in your deployment (or potentially identify unused indexes to remove). For a start on log-based analysis, see Understanding IXSCAN and COLLSCAN in MongoDB logs on Server Fault.


Linked Servers pointing to same server (localhost) causing "Transaction context in use by another session" error

Loopback linked servers cannot be used in a distributed transaction . Install two SQL Servers in local to solve your problem. Link one instance to another.

http://msdn.microsoft.com/en-us/library/ms188716(SQL.105).aspx


Approaches towards Marking Membership of Group?

If you just need to know if an ID is marked or not, using your 2nd approach (not registering the ones not marked or marked) has a few more benefits than the first: less rows saved means more unused space, and a EXISTS or NOT EXISTS operation is faster than actually retrieving the row and comparing the BOOL marked against a Y/N.

There is a noticeable difference however:

Approach 1 (using a marked column) has 3 possible variations of
foo: foo exists and is marked, foo exists and it's not marked or foo doesn't exist.
Approach 2 (just using ID) has only 2 variations: either foo exists which we assume it's marked, or it doesn't which we can assume that it's either not marked or it doesn't exist. This brings a problem of discerning these last two scenarios.

On another hand, if you are going to need additional information for foo (like most systems) you will have to add more columns to it.

Imagine that now you need to know the date when it was marked or
unmarked. You can track this with another table, or just add a
ModifiedDate to foo, if on your first approach.
Imagine that now you have another state that's "partially marked" so
a BOOL's 2 possible values (other than NULL) is not enough. You will now need to change it to a VARCHAR, or an INT with a foreign key to another table that explains what that status is. If you had    implemented the 2nd approach, you will have to change a big part of your queries (removing the EXISTS / NOT EXISTS), rather than just a few literals or hard-coded numbers, which adds additional maintenance time.

Another thing to point out is the relationship of this foo to other entities. If another entity might relate to 1 foo (no more than 1) or not, then it would probably be part of that other entity's table, but on the other hand, if an instance of another entity can relate to foo in a 1 - N relationship, then the foreign key must be on foo, which you won't be able to add if you are using the 2nd approach, since an unmarked foo has no record.

From your question title I can guess that this foo represents the link between a User and a Group. If that were the case, the common approach would be using a User table with UserID as PK, a Group table with GroupID as PK and a many to many breakdown table UserByGroup with UserID, GroupID as composite PK, each being a foreign key to the related table. You could store the CreatedDate in this table if you need additional info, for example.


A view to show worked time per task per user?

it's better to add a userID to your worketime table instead of User and also the name user is reserved word so don't use it. The view you are looking for is made like this

CREATE VIEW user_task_time as select `user`,taskid,sec_to_time(sum(TIME_TO_SEC(TIMEDIFF(endwork,startwork)))) as timeWorked FROM worktime GROUP BY user,taskid;

TIMEDIFF() return a time value and it is converted to second and back to time because SUM() function does not return correct results when used by time data type.


Cannot start Oracle Enterprise 13C after stopping OMS

After all the searching, I could not find any help, until I figured out the problem myself. In order to use enterprise manager, the pluggable database container must be accessible and open before starting the oracle management service.

This can be done through enterprise manager 12C:

Access your pluggable database containers shown below

You will see that container database is down by open-mode column. Click on actions and say open all or the one you want:

Choose the appropriate action(s) to open your PDB's

Or you could run the below query to open all pluggable databases from sqlplus [I tried it from sqlplus and it did not want to work for me]:

alter pluggable database ALL close IMMEDIATE ;
alter pluggable database ALL open read write;

If it works you should see the open-mode in green shown below in enterprise manager 12C:

After starting the PDB, you should be able to start the Enterprise manager 13C OMS as shown below:

#!/bin/bash
export ORACLE_HOME=/u01/app/oracle/product/12.2.0.1/db_1
export OMS_HOME=/u01/app/oracle/middleware
export AGENT_HOME=/u01/app/oracle/agent/agent_inst

# Start everything
$ORACLE_HOME/bin/dbstart $ORACLE_HOME

$OMS_HOME/bin/emctl start oms
$AGENT_HOME/bin/emctl start agent


Is my MySQL instance irretrievably dead?

Looks like you installed MySQL 5.1 over a 5.0 (or older) installation, without running mysql_upgrade to create new system tables and add columns to existing system tables. –  Bill Karwin 25 mins ago

It is safe to run mysql_upgrade without disturbing your other databases.

Read http://dev.mysql.com/doc/refman/5.1/en/mysql-upgrade.html for more information


You'll need to either use an ETL Tool eg Microsoft SSIS. There's plenty of open source products available too. Or you can build your own extract utility using a programming language like PHP, .Net etc.

The Kimball guys have an article on whether you should build your own or use an off the shelf product.
http://www.kimballgroup.com/2008/04/should-you-use-an-etl-tool/

To schedule the load, you can use windows scheduler on Windows or cron jobs on Unix.


The easiest way I see it is a batch file using sqlcmd:

sqlcmd -S .\SQL2008R2 -E -Q "dbcc loginfo" >> log.txt

The redirect operator appends data at the end of the file.

-Q parameter was used for closing the sqlcmd session immediately.


Double queue reader or is it still busy?

Solved it.

The Queue Reader agent is only used in snapshot or updatable transactional replications.

I just deleted the job and emptied tables MSqreader_agents & MSqreader_history from the distribution database. Finally I killed the qrdrsvc.exe process.

Helpful links:
http://social.msdn.microsoft.com/Forums/en-US/a6de412b-ebae-4a39-b00f-8f724237e14e/queue-reader-agent-error
http://technet.microsoft.com/en-us/library/ms147378.aspx


Postgres: insert new row in table if the table does not have records where column_name = 'abc' occurs N times?

You can do something with triggers
Or you do somewhat complexer INSERT
Which allows only 5 John in your table

CREATE TABLE "user" ("first_name" varchar(16),"last_name" varchar(50) )

✓

insert into "user"
select 'john', 'doe'
where (select count(*) from "user" where "first_name" = 'john') < 5

1 rows affected

insert into "user"
select 'john', 'doe2'
where (select count(*) from "user" where "first_name" = 'john') < 5

1 rows affected

insert into "user"
select 'john', 'doe3'
where (select count(*) from "user" where "first_name" = 'john') < 5

1 rows affected

insert into "user"
select 'john', 'doe4'
where (select count(*) from "user" where "first_name" = 'john') < 5

1 rows affected

insert into "user"
select 'john', 'doe5'
where (select count(*) from "user" where "first_name" = 'john') < 5

1 rows affected

insert into "user"
select 'john', 'doe6'
where (select count(*) from "user" where "first_name" = 'john') < 5

✓

SELECT * FROM "user"

first_name | last_name
:--------- | :--------
john       | doe
john       | doe2
john       | doe3
john       | doe4
john       | doe5

db<>fiddle here


Select different combinations for same table msSQL

Looks like you want Cross Joins:

SELECT t1.OS_ID, t2.OS_ID, t3.OS_ID
FROM tab AS t1
CROSS JOIN tab AS t2
CROSS JOIN tab AS t3
WHERE t1.OZ_ID = 1
  AND t2.OZ_ID = 2
  AND t3.OZ_ID = 3


How to get a list of stored procedure along with their execution count in oracle 12c?

You can list them with this query (similar to sys.procedures):

SELECT * FROM USER_PROCEDURES

For execution count, I think you will have to use Audit functionnalities.


SQL Server Management Studio generates error, can't start

Instead of running repair, completely uninstall the client tools using setup, and then reinstall them from scratch. Make sure you also uninstall any add-ins or plug-ins that you're currently using in SSMS. Once you've installed SSMS from the media, then apply SQL Server 2008 R2 Service Pack 1. If you have Visual Studio 2010 also installed on your machine, make sure you've installed service pack 1 there before re-installing client tools.

FWIW, I've found the new features and enhancements in SQL Server 2012's version of Management Studio to be very beneficial, and have been using it as my only SSMS version since the early betas to manage 2005, 2008, 2008 R2 and 2012 instances with no problems. RC0 is out so it may be worth trying that version too. It's a big download for just the client tools, but worth it IMHO. You can get it here:

http://www.microsoft.com/download/en/details.aspx?id=28145


Check data is the same after restore SQL

First, CHECKSUM and VERIFYONLY do not do what you think they do.  Backing up with CHECKSUM merely verifies that the page checksums (if there and valid) are valid when writing the backup file.  VERIFYONLY only verifies that the backup file is complete and readable.

But none of this will protect you from corruption in the source database, it is entirely possible for there to be corrupt pages, have them backed up and then restore them into the new database.

What and why are you trying to do the verificaton?  You can do row counts for each table and compare that way.  RedGate has a comparison tool (paid, but with a trial) that can compare and display the differences.


How do I record SQL transactions with SQL Server Express

You'll need a profiler of some variety if the call is wrapped up.

One of:

Download a trial version, install client tools only
3rd party http://sites.google.com/site/sqlprofiler/


Vertical / Horizontal Scaling Disk Size MySQL or MariaDB

What you're looking for is typically called shared-data architecture, where multiple database nodes access the same set of database files (a database) distributed across the nodes, and the database capacity approximates the sum of disk and CPU capacities of all nodes (more or less).

The MySQL cluster solutions you're referring to implement the shared-nothing architecture, where the database copies are replicated between nodes, and each database node works with its own "copy", so you're still limited by the disk and processor capacity of an individual node.

I don't think there exists a shared-data cluster solution for MySQL, as implementing it is a non-trivial endeavour -- just ask Oracle RAC folks.

If your workload allows, you might consider a cluster file system, such as CephFS or GlusterFS, which will present the sum of all servers' disk volumes as a single file system to a single database node. This will at least address your disk capacity requirement.


Transaction has set autocommit = 0, but still the update happens before I commit

In MySQL most DDL statements, including ALTER TABLE, implicitly issue a COMMIT before executing. It's described in the manual.

One way to do what you want is reorder your statements, placing updates after the alter.