FreeTextTable - Query as a set operation vs. having to cursor

Hmm... try this "trick" for your function FN_GET_CLOSEST_CLASS_CODE_DESCRIPTION_ID_TBL to correlate with a SQL table variable rather than the freetext query:

create function FN_GET_CLOSEST_CLASS_CODE_DESCRIPTION_ID_TBL (
     @classCodeDescription  varchar(4000)
    ,@state                 varchar(10)
    ,@classCode             int
    ,@effectiveOn           datetime2)
RETURNS @ClosestClassCodeDesc TABLE
(
     TBLRANK INT,
     CLASS_CODE_DESCRIPTION_ID  varchar(4000)
)
as
begin
    insert into @ClosestClassCodeDesc (TBLRANK, CLASSname)
    select KEY_TBL.RANK, cd.CLASS_CODE_DESCRIPTION_ID from CLASS_CODE_DESCRIPTION cd
  join CLASS_CODE cc on cc.CLASS_CODE_ID = cd.CLASS_CODE_ID
  inner join freetexttable (CLASS_CODE_DESCRIPTION, DESCRIPTION, @classCodeDescription, 1) as KEY_TBL on cd.CLASS_CODE_DESCRIPTION_ID = KEY_TBL.[KEY]
where cc.CLASS_CODE = @classCode
  and cc.STATE = @state
  and cc.EFFECTIVE_DATE <= @effectiveOn
  and cc.EXPIRATION_DATE > @effectiveOn
  and cd.EFFECTIVE_DATE <= @effectiveOn
  and cd.EXPIRATION_DATE > @effectiveOn
return
end


I don't think you need a sub query here. Does this give you what you need? If so it should be more efficient.

SELECT m.title, MAX(r.stars)
FROM Movie AS m
LEFT OUTER JOIN rating AS r ON r.mID = m.mID;
GROUP BY m.title

Only way it could break that I can see is if you have two movies with the same title and different mID's.


Make MySQL print a timestamp at each time it starts

You do not need to consult the log. mysqld can tell you.

For anyone running MySQL 5.6 and prior, or MySQL 5.7 in MySQL 5.6 compatibility mode

SELECT NOW() - INTERVAL variable_value SECOND MySQL_Started
FROM information_schema.global_status
WHERE variable_name='Uptime';

For everyone running MySQL 5.7

SELECT NOW() - INTERVAL variable_value SECOND MySQL_Started
FROM performance_schema.global_status
WHERE variable_name='Uptime';

EXAMPLE (MySQL 5.7.12 on my Windows Laptop)

mysql> SELECT NOW() - INTERVAL variable_value SECOND MySQL_Started
    -> FROM performance_schema.global_status
    -> WHERE variable_name='Uptime';
+----------------------------+
| MySQL_Started              |
+----------------------------+
| 2018-03-19 09:10:43.000000 |
+----------------------------+
1 row in set (0.00 sec)

As to why the time is not being printed, here is what I suspect

mysqld_safe starts mysqld and stays in an indefinite loop
mysqld terminates over some minor condition

Out of memory
Too many connections
Accessing a corrupted table

mysqld is started again in the indefinite loop
I mentioned this Mar 05, 2013 : mysqld_safe version different than mysqld?

I do not believe mysqld_safe will print timestamps on mysqld restarts. If you really want mysqld_safe to print a timestamp on mysqld restarts, you can edit mysqld_safe (it is just a bash script) and place a line in the indefinite loop that echoes the date


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

I think the problem could be due to the nested query being >= and not =.

This gives the output you described:

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

Memo - Can you please post what your expected results would be for all fields and not just the ID ordering.


Based on your description of the problem it is unlikely that the UDF is the cause of the cardinality estimation errors. You can try commenting out the UDF and checking if the join estimate changes if you want to test that.

It is possible to experience cardinality estimation errors even when statistics have been updated with FULLSCAN. The histograms created by SQL Server may not model your data in a way for the optimizer to create good enough plans, SQL Server may not have histograms that exactly match the joined data (you could be filtering on the inner and/or outer table), you may be joining on multiple columns which complicate estimations, and so on. For example, Microsoft changed some of the join cardinality estimate calculations with the new CE released in SQL Server 2014. Some tables will have data that better matches the assumptions for the legacy CE and some tables will have data that better matches the assumptions for the new CE.

For the first estimation issue (38999 estimated rows versus 701680 actual rows), based on what you've provided I can only recommend reading about join cardinality calculations. Microsoft released a white paper for the new cardinality estimator in SQL Server 2014. I also know of a blog post that dives into the internals of join cardinality quite a bit. As the question is written there are too many possible causes to give a good answer. Please post an actual execution plan if you'd like further guidance.

For the second estimation issue (1 estimated row versus 1546894 actual rows), note that the estimated number of rows is for a single iteration of the inner loop for the join. The actual number of rows is for all rows returned from the table over all iterations of the join, so they will not often match for nested loop joins. What you are seeing is common and not necessarily a sign of a problem.


Very easy to do with a DDL trigger on your database:

CREATE TRIGGER RequireSchemaBinding ON DATABASE FOR CREATE_VIEW, ALTER_VIEW
AS
DECLARE @object nvarchar(max)
DECLARE @schema nvarchar(max)
SET @object = EVENTDATA().value('(/EVENT_INSTANCE/ObjectName)[1]', 'nvarchar(max)')
SET @schema = EVENTDATA().value('(/EVENT_INSTANCE/SchemaName)[1]', 'nvarchar(max)')
IF ISNULL(OBJECTPROPERTY(OBJECT_ID(QUOTENAME(@schema) + '.' + QUOTENAME(@object)), 'IsSchemaBound'), 0) = 0
BEGIN
    RAISERROR('Not today, punk.', 16, 1)
    ROLLBACK TRANSACTION
END


What does "GIN doesn't support full index scans" mean?

a word or two belonging to a pre-set, almost 'constant', list of
  words.

Have you considered scanning the texts as they are input and flagging them (possibly with a many-to-many link to another table containing your list of keywords)?


Adding CASE statement to flag percentage available less than 5% as red ELSE green

In this instance replacing td = CAST([PercentUsed]as varchar(30)), with something like:

td = ' 95 THEN '#FF0000' ELSE '#40C211' + '">'
   + CAST([PercentUsed]as varchar(30))
   + ''
   ,

is probably what you are looking for. Or if you need to use font tags instead of more modern styling options (perhaps because some of your recipients have truly ancient email readers) then edit as needed.

Your inner query is using the FOR XML hack* to concatenate everything into a string, the column names forming tags (in this case they are all td) and the PATH parameter meaning each row gets wrapped in
/
. For more information on forming XML document fragments (or in this case, HTML document fragments) this way see the official documentation such as https://docs.microsoft.com/en-us/sql/relational-databases/xml/for-xml-sql-server and for various tutorials on using this for string building more generally search for "TSQL FOR XML concatenation" or similar.

* I call it a hack as you are not actually returning an XML typed result, which is what the feature is designed for, it is being used here in a way that makes it behave like a string concatenation aggregate, the most common use for FOR XML in my experience, see articles like https://sqlblog.org/2011/03/08/t-sql-tuesday-16-this-is-not-the-aggregate-youre-looking-for for notes on this and other techniques to achieve the same result.


PostgreSQL : Prevent SQL Injection in pl/pgsql function

Use parameter placeholders instead of concatenating the input into the SQL string.

create function A(in startdate varchar(10), in enddate varchar(10))
  RETURNS setof AB AS $$
declare
  l_startdate date;
  l_enddate date;
BEGIN
 l_startdate := to_date(startdate,'YYYY-MM-DD');
 l_enddate := to_date(enddate,'YYYY-MM-DD');

 return query execute
 'select rid,dcid,
        sum(case when id between 10 and 19 or id between 21 and 26 then noOfcaptured else 0 end) total_tilldate,
        sum(case when dateofevent >= $1 and (id between 10 and 19 or id between 21 and 26) then noofcaptured else 0 end) total_range,
        sum(case when id not between 51 and 59 then cast(sizecaptured as double precision)/1048576 else 0 end) totalsize_tilldate,
        sum(case when dateofevent >= $2 and id not between 51 and 59 then cast(sizecaptured as double precision)/1048576 else 0 end) totalsize_range
   from repostats
  where dateofevent<= $3
  group by rid, dcid'
    using l_startdate, l_startdate, l_enddate;
END;
$$ LANGUAGE plpgsql;


Neither index appears to be "covering". To avoid a key lookup, you must at least INCLUDE every column involved in your SELECT, WHERE, and ORDER BY.

The reason the older index is being used is that it's better for the query. That's partially because the index on Vit_DataAberturaReal prevents a sort operator in the plan - because the data is already sorted properly. It's also because the first column in the new index is useless in your query non-sargable. If you can remove the RTRIM, LTRIM, and LOWER, then the performance may be better.

If the index is only to support this query, you may consider adding WHERE Vit_codigoStatus IN (3,4) to your index definition.

Ideally, the WHERE columns would be part of the index itself - not in the INCLUDE. If I were to make an index specifically to support this query, it would probably be along the lines of:

CREATE INDEX [IX_SELECTTOP20_INCLUDE]
    ([Vit_DataAberturaReal])
INCLUDE(
    [Vit_Codigo], [Vit_Nome], [Vit_Apelido], [Vit_codigoStatus],
    [Vit_Numero], [Vit_Ano], [Vit_CodigoTiposExames],
    [Vit_CodigoEspecialidade], [Vit_Rg])
WHERE [Vit_Rg] = 'n\a' AND [Vit_codigoStatus] IN ( 3, 4 )

Note: this will not support the LTRIM(RTRIM(LOWER(...))).


Better way to select a matching subset of rows?

If I understood you correctly, your query can be simplified to:

select house_id
from house_to_cats
group by house_id
having array_agg(cat_id order by cat_id) = array[1,2,3]

Note the order by in the array_agg() call - the array [3,2,1] is not equal the array [1,2,3]. To avoid incorrect results due to the aggregation being done in a different order, the aggregated array has to contain the values in the same order as the comparison value.

Online example: https://rextester.com/IKGHWL59301


Imho, it's worth to try the following:
Simulate the same scenario but with a tiny test db. Don't forget to do full/log backups which happened (?) with your big db on primary since Sunday.
It will take ~10-15 mins to reproduce and you will know for sure will it go or not.
Regarding the question:  I believe it will not start auto-seeding because your DB was recovered and readable before you removed it from HAG. Also, if there was a full backup on primary, it also affects the log chain.


Efficient matching rows across two tables with huge data set

Without making any schema change, your solution requires a table scan on both sides - that's the most efficient you'll be able to do.  You simply have to LEFT JOIN A to B and compare every field.

However, if you expect to do this in the future, you can do a couple things to make life easier: you can add a calculated hash, which will almost always detect a change.  Compare this field, and you can identify changes.  Add an index to each table with the PK field(s) and this hash field, and you can very quickly find new and changed records:

SELECT
    A.PK,
    CASE
        WHEN B.PK IS NULL THEN 'New'
        WHEN A.Hash <> B.Hash THEN 'Changed'
        ELSE 'Unchanged' END AS Status
FROM
    A
    LEFT JOIN B ON A.PK = B.PK

Microsoft SQL offers a ROWVERSION construct which makes this type of operation simple; it looks like the equivalent in Oracle is system change numbers: https://stackoverflow.com/a/20488598/565869.

If for any reason that doesn't work for you, you could always roll your own using triggers or by modifying the sprocs which update these tables (you are wrapping your UPDATEs in sprocs, right?)


Why is selecting all resulting columns of this query faster than selecting the one column I care about?

It's true that selecting more columns implies that SQL Server may need to work harder to get the requested results of the query. If the query optimizer was able to come up with the perfect query plan for both queries then it would be reasonable to expect the SELECT * query to run longer than the query that selects all columns from all tables. You have observed the opposite for your pair of queries. You need to be careful when comparing costs, but the slow query has a total estimated cost of 1090.08 optimizer units and the fast query has a total estimated cost of 6823.11 optimizer units. In this case, it could be said that the optimizer does a poor job with estimating total query costs. It did pick a different plan for your SELECT * query and it expected that plan to be more expensive, but that wasn't the case here. That type of mismatch can happen for many reasons and one of the most common causes is cardinality estimate problems. Operator costs are largely determined by cardinality estimates. If a cardinality estimate at a key point in a plan is inaccurate then the total cost of the plan may not reflect reality. This is a gross oversimplification but I hope that it will be helpful for understanding what's going on here.

Let's start by discussing why a SELECT * query might be more expensive than selecting a single column. The SELECT * query may turn some covering indexes into noncovering indexes, which might mean that the optimizer needs to do addition work to get all of the columns it needs or it may need to read from a larger index. SELECT * may also result in larger intermediate result sets which need to be processed during query execution. You can see this in action by looking at the estimated row sizes in both queries. In the fast query your row sizes range from 664 bytes to 3019 bytes. In the slow query your row sizes range from 19 to 36 bytes. Blocking operators such as sorts or hash builds will have higher costs for data with a larger row size because SQL Server knows it's more expensive to sort larger amounts of data or to turn it into a hash table.

Looking at the fast query, the optimizer estimates that it needs to do 2.4 million index seeks on Database1.Schema1.Object5.Index3. That is where most of the plan cost comes from. Yet the actual plan reveals that only 1332 index seeks were done on that operator. If you compare the actual to the estimated rows for the outer parts of those loop joins you'll see large differences. The optimizer thinks that many more index seeks will be needed to find the first 1000 rows needed for the query's results. That's why the query has a relatively high cost plan but finishes so quickly: the operator that was predicted to be the most expensive did less than 0.1% of its expected work.

Looking at the slow query, you get a plan with mostly hash joins (I believe the loop join is there just to deal with the local variable). Cardinality estimates definitely aren't perfect, but the only real estimate problem is right at the end with the sort. I suspect most of the time is spent on the scans of the tables with hundreds of millions of rows.

You may find it helpful to add query hints to both versions of the query to force the query plan associated with the other version. Query hints can be a good tool to figure out why the optimizer made some of its choices. If you add OPTION (RECOMPILE, HASH JOIN) to the SELECT * query I expect you'll see a similar query plan to the hash join query. I also expect that query costs will be much higher for the hash join plan because your row sizes are much bigger. So that could be why the hash join query wasn't chosen for the SELECT * query. If you add OPTION (LOOP JOIN) to the query that selects just one column I expect you'll see a query plan similar to the one for the SELECT * query. In this case, reducing the row size shouldn't have much of an impact on the overall query cost. You might skip the key lookups but that's a small percentage of the estimated cost.

In summary, I expect that the larger row sizes needed to satisfy the SELECT * query push the optimizer towards a loop join plan instead of a hash join plan. The loop join plan is costed higher than it should be due to cardinality estimate issues. Reducing the row sizes by selecting just one column greatly reduces the cost of a hash join plan but probably won't have much of an effect on the cost for a loop join plan, so you end up with the less efficient hash join plan. It's hard to say more than this for an anonymized plan.


I have addressed this once before : MySQL database drop insanely slow

My suggestion in that post was this

SET unique_checks = 0;
SET foreign_key_checks = 0;
SET GLOBAL innodb_stats_on_metadata = 0;
DROP DATABASE db_madeintouch;
SET GLOBAL innodb_stats_on_metadata = 1;
SET foreign_key_checks = 1;
SET unique_checks = 1;

If all your data uses InnoDB, this might work for you.

Four years ago, I thought having tons of users was also a symptom.

If a patch was missed, here is another likely suspect: Bug #61188 DROP TABLE extremely slow

  [15 Aug 2011 22:14] James Day

  The fix for this is in MySQL 5.5.15 and is expected to be in 5.1.59. At the time of writing it hasn't yet been added to the  release notes but that should happen soon.

  [7 Oct 2011 0:46] John Russell
    Added to changelog:

  The DROP TABLE command for an InnoDB table could be very slow, in a
    configuration with a combination of table compression, partitioning,
    and a large buffer pool.

Please give it a try and see tell us if it worked.


How to format the output of this query into year...second?

you can try Right('0'+ Cast(expression as varChar(3)), 2) like this:

SELECT Right('0' + Cast( FLOOR ( UpTime / 31207680 ) as varChar(3)), 2) + ':' -- YEARS
  + Right('0' + Cast( FLOOR( (UpTime / 2600640 ) - FLOOR ( UpTime / 31207680  )  * 12 )  as varChar(3)), 2) + ':' --  MONTHS
  + Right('0' + Cast( FLOOR( (UpTime / 604800 ) - FLOOR ( UpTime / 2600640 )  * 4.3 )  as varChar(3)), 2) + ':' --  WEEKS
  + Right('0' + Cast( FLOOR( (UpTime / 86400 ) - FLOOR( UpTime / 604800 ) * 7 )  as varChar(3)), 2) + ':' --  DAYS
  + Right('0' + Cast( FLOOR( ( UpTime / 3600 ) - FLOOR( UpTime / 86400 ) * 24 )  as varChar(3)), 2) + ':' --  HOURS
  + Right('0' + Cast( FLOOR( ( UpTime / 60 )   - FLOOR( UpTime / 3600 ) * 60 )  as varChar(3)), 2) + ':' --  MINUTES
  + Right('0' + Cast( UpTime - FLOOR( UpTime / 60 ) * 60  as varChar(3)), 2)  --  SECONDS
FROM (
    select @secs as Uptime
   ) AS RadheX


How to set 'timing on' permanently in PostgreSQL?

\timing is specific to the client psql, not to the database server. You need to put that into the configuration file for psql which is ~/.psqlrc

See the manual for details: https://www.postgresql.org/docs/current/static/app-psql.html#AEN100589

  psql attempts to read and execute commands from the system-wide startup file (psqlrc) and then the user's personal startup file (~/.psqlrc), after connecting to the database but before accepting normal commands


How to completely hide a function from all other users and roles?

You can revoke and grant access privileges from/to specific Postgres roles but the name and source of the function will be still visible.

To keep the function in top secret create it in the session in which it is needed, in the schema pg_temp. It is a schema associated with the session for keeping temporary resources. It is automatically created when needed and automatically dropped at the end of the session.

create or replace function pg_temp.top_secret_function()
returns text language sql as $$
    select 'Top secret!'::text;
$$;

select pg_temp.top_secret_function();

CREATE FUNCTION
 top_secret_function
---------------------
 Top secret!
(1 row)

There is no need to drop the function, it will be removed when the session terminates. Nobody (even you as the same user in another concurrent session) can see the function.


MS SQL Server 2008 makes hard disk space disappear

Try running a tool like http://www.uderzo.it/main_products/space_sniffer/ (or one of the many alternatives) to scan the drive and see what is taking the space. Remember to run it elevated so that it can see any protected files that might be large enough to be significant.


CMDEXEC The process could not be created for step... (reason: Access is denied). The step failed

ok, so instead of using a CMDEXEC job step, I changed it back to a t-SQL script step replaced the batch file name with the full string:

EXEC xp_cmdshell 'c:\dircommand.bat'

and it works.  So I am not sure what the problem is with Operating System job steps but at this point I am passed the hurdle.  funny thing is, I have another server setup the other way and it works fine.  Thanks for the offers of assistance.


Can I let PostgreSQL report the offending rows when a multi-row INSERT fails because of mismatched geometry types?

The problem here is that the types aren't mismatched. PostGIS provides very few PostgreSQL types, namely:

box2d — A box composed of x min, ymin, xmax, ymax. Often used to return the 2d enclosing box of a geometry.
box3d — A box composed of x min, ymin, zmin, xmax, ymax, zmax. Often used to return the 3d extent of a geometry or collection of geometries.
geometry — Planar spatial data type.
geometry_dump — A spatial datatype with two fields - geom (holding a geometry object) and path[] (a 1-d array holding the position of the geometry within the dumped object.)
geography — Ellipsoidal spatial data type.

That said, there is clearly no check on different subtypes of geometry.  From point or multipoint, a violation of subtype causes the transaction to fail.

Create an ETL script that loads into a simple of geometry, then you can select the types that are not of the subtype with ST_GeometryType, or GeometryType

CREATE EXTENSION postgis;

CREATE TABLE t (
    id integer,
    p geometry
);

INSERT INTO t
VALUES
    ( 1, ST_GeometryFromText('Point(0 0)')      ),
    ( 2, ST_GeometryFromText('Point(1 2)')      ),
    ( 3, ST_GeometryFromText('MultiPoint(2 3)') ),
    ( 4, ST_GeometryFromText('Point(5 23)')     ),
    ( 5, ST_GeometryFromText('Point(42 36)')    );

Now you can run,

SELECT id, ST_AsText(p)
FROM t
WHERE GeometryType(p) <> 'POINT';

And you'll get,

 id |    st_astext
----+-----------------
  3 | MULTIPOINT(2 3)
(1 row)

Alternatively, you can avoid this problem by casting all types to MultiPoint with ST_Multi().


Update column from Base64 to string (natively in SQL)

Since you haven't given us any information about the table or column in question you'll have to update my example with the correct information.

update thetable set thecolumn = FROM_BASE64(thecolumn)


The Filter condition is in Me.Filter: you use it above.

You'd have to parse this, but you must have set it previously to be able to apply it. Ignoring SQL Injection risk (I just got this off the interwebs), you know in this snippet you are filtering on ProductCatID

Me.Filter = "ProductCatID = """ & Me.cboShowCat & """"
Me.FilterOn = True

You can set a new variable/property (eg Me.Tag) for use in the Header/Footer


When there are multiple data files in a database, all the writes to the database are striped across all files based on the proportion of free space that the file has to the total free space across all of the files. Now, each of the data files has its own set of allocation pages (called PFS, GAM, and SGAM pages) so as the writes move from file to file the page allocations occur from different allocation bitmap pages, spreading the work out across the files and reducing the contention on any individual page. This would be your benefit.

Again important factor is how many of them are there. As a general recommendation it should be based on logical processors you have on the server. So for 8 or less keep the number equal to processors. For more starting point should be 8 and then increase by one based on testing or contention issues.

Again for write latencies you might want to check with your storage team what's the threshold for the acceptance number in your environment. For faster temp db we in out environment keep them on SSDs and make sure log file is spiltted on different drive than data files.

Also as a side note those latencies number may or may not be a problem depending upon the nature of performance you are witnessing right now.

Plus I would suggest to check on trace flags 1117 and 1118 if you are on version of sql running 2014 or lower. From 2016 onwards it's enabled by default


Append duplicate records with count (mysql)

From the sake of this question, I will call the source table aaronlax

PROPOSED SOLUTION

SET @ndx = 0;
SET @cur_hash = '';
SELECT entry_id,CONCAT(url_title,IF(ndx>0,CONCAT('-',ndx),'')) url_title
FROM (SELECT
   @new_hash := MD5(url_title),
   (@ndx := IF(@new_hash=@cur_hash,@ndx+1,0)) ndx,
   @cur_hash := MD5(url_title),
   url_title,entry_id
FROM (SELECT url_title,entry_id FROM aaronlax ORDER BY url_title) AA) A;

SAMPLE DATA

use test
DROP TABLE IF EXISTS aaronlax;
CREATE TABLE aaronlax
(
    entry_id int(10) unsigned NOT NULL AUTO_INCREMENT,
    url_title varchar(75) NOT NULL,
    PRIMARY KEY (entry_id),
    KEY (url_title)
) ENGINE=MyISAM;
INSERt INTO aaronlax (url_title) VALUES
('a-stoney'),('a-stoney'),
('ab-dille'),('ab-dille'),
('ab-dille'),('ac-gifta'),
('ae-jones'),('ae-jones');

SAMPLE DATA LOADED

mysql> use test
Database changed
mysql> DROP TABLE IF EXISTS aaronlax;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE aaronlax
    -> (
    ->     entry_id int(10) unsigned NOT NULL AUTO_INCREMENT,
    ->     url_title varchar(75) NOT NULL,
    ->     PRIMARY KEY (entry_id),
    ->     KEY (url_title)
    -> ) ENGINE=MyISAM;
Query OK, 0 rows affected (0.02 sec)

mysql> INSERt INTO aaronlax (url_title) VALUES
    -> ('a-stoney'),('a-stoney'),
    -> ('ab-dille'),('ab-dille'),
    -> ('ab-dille'),('ac-gifta'),
    -> ('ae-jones'),('ae-jones');
Query OK, 8 rows affected (0.00 sec)
Records: 8  Duplicates: 0  Warnings: 0

mysql> select * from aaronlax;
+----------+-----------+
| entry_id | url_title |
+----------+-----------+
|        1 | a-stoney  |
|        2 | a-stoney  |
|        3 | ab-dille  |
|        4 | ab-dille  |
|        5 | ab-dille  |
|        6 | ac-gifta  |
|        7 | ae-jones  |
|        8 | ae-jones  |
+----------+-----------+
8 rows in set (0.00 sec)

mysql>

PROPOSED SOLUTION EXECUTED

mysql> SET @ndx = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> SET @cur_hash = '';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT entry_id,CONCAT(url_title,IF(ndx>0,CONCAT('-',ndx),'')) url_title
    -> FROM (SELECT
    ->    @new_hash := MD5(url_title),
    ->    (@ndx := IF(@new_hash=@cur_hash,@ndx+1,0)) ndx,
    ->    @cur_hash := MD5(url_title),
    ->    url_title,entry_id
    -> FROM (SELECT url_title,entry_id FROM aaronlax ORDER BY url_title) AA) A;
+----------+------------+
| entry_id | url_title  |
+----------+------------+
|        1 | a-stoney   |
|        2 | a-stoney-1 |
|        3 | ab-dille   |
|        4 | ab-dille-1 |
|        5 | ab-dille-2 |
|        6 | ac-gifta   |
|        7 | ae-jones   |
|        8 | ae-jones-1 |
+----------+------------+
8 rows in set (0.00 sec)

mysql>

GIVE IT A TRY !!!

CAVEAT

In case you are wondering why the proposed solution works, let's take a look at the subqueries

INNERMOST SUBQUERY

mysql> SELECT url_title,entry_id FROM aaronlax ORDER BY url_title;
+-----------+----------+
| url_title | entry_id |
+-----------+----------+
| a-stoney  |        1 |
| a-stoney  |        2 |
| ab-dille  |        3 |
| ab-dille  |        4 |
| ab-dille  |        5 |
| ac-gifta  |        6 |
| ae-jones  |        7 |
| ae-jones  |        8 |
+-----------+----------+
8 rows in set (0.00 sec)

mysql>

NEXT LEVEL SUBQUERY

mysql> SELECT
    ->    @new_hash := MD5(url_title),
    ->    (@ndx := IF(@new_hash=@cur_hash,@ndx+1,0)) ndx,
    ->    @cur_hash := MD5(url_title),
    ->    url_title,entry_id
    -> FROM (SELECT url_title,entry_id FROM aaronlax ORDER BY url_title) AA;
+----------------------------------+------+----------------------------------+-----------+----------+
| @new_hash := MD5(url_title)      | ndx  | @cur_hash := MD5(url_title)      | url_title | entry_id |
+----------------------------------+------+----------------------------------+-----------+----------+
| 911adc6db360f218da5069b20cfb1917 |    0 | 911adc6db360f218da5069b20cfb1917 | a-stoney  |        1 |
| 911adc6db360f218da5069b20cfb1917 |    1 | 911adc6db360f218da5069b20cfb1917 | a-stoney  |        2 |
| b468c0dd0ef78fde7cf5ae14765f9a5a |    0 | b468c0dd0ef78fde7cf5ae14765f9a5a | ab-dille  |        3 |
| b468c0dd0ef78fde7cf5ae14765f9a5a |    1 | b468c0dd0ef78fde7cf5ae14765f9a5a | ab-dille  |        4 |
| b468c0dd0ef78fde7cf5ae14765f9a5a |    2 | b468c0dd0ef78fde7cf5ae14765f9a5a | ab-dille  |        5 |
| 3fff0367af8bf5d3b3b0089b8f187c04 |    0 | 3fff0367af8bf5d3b3b0089b8f187c04 | ac-gifta  |        6 |
| 1909ba0f7a57ca081c82d2f7ba54c9e5 |    0 | 1909ba0f7a57ca081c82d2f7ba54c9e5 | ae-jones  |        7 |
| 1909ba0f7a57ca081c82d2f7ba54c9e5 |    1 | 1909ba0f7a57ca081c82d2f7ba54c9e5 | ae-jones  |        8 |
+----------------------------------+------+----------------------------------+-----------+----------+
8 rows in set (0.00 sec)

mysql>

From this level, I only need three columns: entry_id, url_title, ndx in the outer most level

Then, I append to the url_title a hyphen and ndx if ndx is nonzero


Your UPDATE statement looks correct.  Here's "proof":

mysql> SELECT HEX(REPLACE( " !", ' !',' !'));
+----------------------------------+
| HEX(REPLACE( " !", ' !',' !'))   |
+----------------------------------+
| E2808921                         |
+----------------------------------+

E28089 is the hex for the utf8 encoding for "THIN SPACE" (Unicode codepoint 8201=x2009).

And I suggest you do want utf8, not unicode.

You can nest the replaces:

UPDATE wp_posts SET post_content =
        REPLACE(
        REPLACE(post_content,' !',' !'),
                             ' ?',' ?');

Or, for something wrong with the client (phpmyadmin),

UPDATE wp_posts SET post_content =
        REPLACE(
        REPLACE(post_content,' !', UNHEX('E2808921')),
                             ' ?', UNHEX('E280893F'));

This assumes that post_content is declared CHARACTER SET utf8 or utf8mb4.


Start with the open source sp_BlitzCache (disclaimer: I'm the maintainer of the project.) It's a script that analyzes your cached execution plans to find the most resource-intensive ones, and then tells you about design issues in those queries. You don't have to set anything up in advance - just install sp_BlitzCache and then run it:

sp_BlitzCache @SortOrder = 'writes'

Scroll across the result sets until you get to the "Writes" columns:

That'll show you which queries are doing the most writes. You can scroll back across to the left to see the queries, their execution plans, and their anti-pattern warnings, and then work with your developers to improve them. (Or they might be totally normal: for example, based on your metrics, this might just be a case of someone logging data to tables.)


What to do when a field in a table approaches the max signed or unsigned 32 bit integer?

You would generally use integers rather than varchars because they consume less space, have well understood sorting pattern are fast to index etc. Integers are natural data types of a CPU, and hence performance is generally optimal. Typically an integer is 4 bytes, equivalent to just 4 characters in a (non-unicode) varchar.

If you were worried about running out of space with an INT type, then try BIGINT, which gives you 8-byte numbers. The limit on this is pretty huge, and you'd probably run out of disk space before you reached that limit of records :-)  The performance of BIGINT is also going to be very good, especially as many servers are now 64-bit too.

The answer to the first part of your question about what happens when you run out in INTs is not simple, especially as you said without changing the datatype to BIGINT. Basically there isn't much you can do, and what you may be able to do is limited very much by the nature of the data in your database.  What records are foreign-keyed to this data? Do you still need all the data in that table and the related records?  On the assumption that you could archive off a lot of the initial data (and its related data), then the only thing I can suggest is moving the data out of the table (lets say the first 1 to X million records), and then resetting the identity seed to 1.  There are all sorts of reasons though I wouldn't recommend it - for example there are many bits of code I have seen that do things like check the maximum value of an id field, to see what has just been added, and that would not work (and shouldn't be done). Also, people assume that record N was created before N+1.  No easy answer I think.

Finally, I don't know about MySQL, but SQL Server would give an overflow error if you reached the limit.


Where to put utility (not administration) stored procedures in SQL Server?

I would put them in a Utility database.

Putting stuff in the master database is something to avoid as it stores information related to the system configuration.  If there is a problem with the master database, your SQL Server will go down.  It's probably ok to put normal stored procedures used for server level troubleshooting like sp_WhoIsActive but I would never put a CLR based procedure in there.


Can we create indexes on views in SQL Server?

Indexed views are always in sync with the data in the underlying tables. You can read more about indexed views here.

Be careful going overly enthusiastic with indexed views: they can be a real performance killer on heavily written tables.


When running a script with several commands, how to find out where we're at?

I agree with Aaron that RAISERROR...WITH NOWAIT can be very useful and is probably the way to go if you have full control over the script that is being generated.

However, if a long script is currently executing and you don't have the ability to change the script in order to add RAISERROR calls, there are also less direct ways to get this information.

Test script

Here is a test script you can run to help demonstrate the two approaches below:

SELECT 1
WAITFOR DELAY '00:00:15'
SELECT 2
WAITFOR DELAY '00:00:15'
SELECT 3

sp_whosiasctive

While running this script, you can use sp_whoisactive to view the current server activity.  You can often view the query plan for the specific statement that is currently executing.  In my case, I see the following because the WAITFOR statement is most likely to be running at any given moment in time:

Using sys.dm_exec_requests.statement_start_offset

Alternatively, Conor Cunningham also has a post on extracting the statement from sys.dm_exec_query_stats AND sys.dm_exec_sql_text.  I don't believe this has been incorporated into sp_whoisactive yet, but you can use a query like the following to see both the current executing statement and the overall batch.

SELECT er.session_Id AS spid
    --Use the full batch text and the start/end offset of the currect statement to figure
    --out the SQL that is currently executing. This logic is based on the blog post above
    --but has been updated in light of strange cases in SQL Server that caused the original
    --blog post logic to crash with out of bounds errors on the SUBSTRING operation.
    , SUBSTRING (qt.text
                , (CASE WHEN er.statement_start_offset > DATALENGTH(qt.text)
                    THEN 0 ELSE er.statement_start_offset/2 END)+1
                , (CASE WHEN er.statement_end_offset <= 0 THEN DATALENGTH(qt.text)
                    ELSE er.statement_end_offset
                    END - CASE WHEN er.statement_start_offset > DATALENGTH(qt.text)
                        THEN 0 ELSE er.statement_start_offset/2 END)
                    + 1
                ) AS query
    , qt.text AS parent_query
FROM sys.dm_exec_requests er
JOIN sys.dm_exec_sessions s
    ON s.session_id = er.session_id
    AND s.session_id <> @@SPID      -- Ignore this current statement.
    AND s.is_user_process = 1       -- Ignore system spids.
    AND s.program_name NOT LIKE '%SQL Server Profiler%' -- Ignore profiler traces
OUTER APPLY sys.dm_exec_sql_text(er.sql_handle)as qt
ORDER BY spid


Why wont SSMS remember my tables when I connect to the server

You can refresh the IntelliSense cache with the key shortcut:

  ctrl+shift+r

Your query window might have lost its connection as well - just:

  Right click on the query window > Connection > Connect

As a little extra: This stackOverflow post discusses custom keyboard shortcuts in ssms - there's loads of custom shortcuts you can setup for anything you might need (in ssms the menu is Tools > Options > Keyboard)


SQL Server Page Life Expectancy sharp drop

How about Database pages, Free list stalls/sec, Page reads/sec, Page lookups/sec, Readahead pages/sec? Their behavior, correlated with the event, would be very useful to know. Similar, all the counters in Memory Manager category.

Could be a query that scans a large amount of cold data hits the server. As it reads the cold data, it evicts pretty much everything else in the BP, including its own just-read pages. After the event the BP slowly start building up the hot pages and PLE start to increase. Counters in Access Methods object could confirm this.


What is "Init for Update" thread state in mariadb?

UPDATEing or DELETEing all of a big table takes a long time.  It is busy copying over every row in case of rollback.  Wait.

Is there some reason for using a sequence instead of an AUTO_INCREMENT?

Is there some reason for renumbering the table?

Neither of those is needed in normal MySQL operation.

To leave the existing column intact, but add a new id that is the PK:

ALTER TABLE t
    DROP PRIMARY KEY,
    ADD COLUMN id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY;


Rank using one column. If it is the same value in 2 rows rank using another column

SELECT *, RANK() OVER (ORDER BY hits DESC, `time` ASC) AS `rank`
FROM table1
ORDER BY id


managing a growing database in the long run (10 years from now)

Here's some generic suggestions...

Don't store the images themselves inside the database (it's unclear if that's your plan). This will cause you huge scaling problems. Store them in a filesystem, or a non-relational database if you must.
The database should be a separate server from the app.
Ideally you should have enough RAM to hold your working data set. Make sure you tune the database in advance (innodb_buffer_pool_size, innodb_log_file_size, etc).
Put your mysql data on the fastest disks you can get. SSD or 10k+ SATA/SAS. Make sure the RAID controller has a battery backup and write caching.
RAID-10 for your mysql data volume.
Put mysql's binlogs (and possibly relay logs, and innodb transaction logs) on a separate RAID array, RAID-1 should be fine.
Have a slave server for read-only queries, to take some load from the master. Your app will have to differentiate RW and RO queries. Set 'read_only=1' on that slave to prevent unfortunate accidents.
As your dataset grows, you may need to consider horizontal sharding across multiple servers. MySQL doesn't provide a mechanism for this, so your app will need to.
There's no reason you can't have a single 2TB database, but you will probably find performance unacceptable for data that doesn't fit in the buffer pool.
Don't go overboard on indexing. Additional indexes beyond the primary introduce additional overhead (not to say you should have none). There may be good opportunities to use a composite index (multiple columns) instead of 2 separate ones.

Hope this helps,


query processor ran out of internal resources - extremely long sql query

The error message is there for a reason. Using UNION on thousands of tables in a single query is performance suicide. Yes, I understand that your reporting application is already in production, but I would still strongly recommend that you to go back to your development team and update the application or, even better, rebuild its logic in a stored procedure instead.

As you suggest in your question, create a temp table, run each of those queries sequentially and dump the results into the temp table. Then, compile the report from the temp table. All of this can be done in a stored procedure, which will also cover other important aspects like security and performance.

This carries a range of advantages:

Temp table can be indexed for performance
You won't bloat your server's query plan cache (and in the process evict pretty much any other query plan that has ever been cached)
The query won't take forever to "compile" (i.e. turn into an execution plan)

I'm not trying to mess with you. It's my firm conviction that if you don't rebuild the report now, you will most likely have to some day, going forward.


Two MySQL query results are different, but shouldn't be

Here's a simpler way of looking at your queries

select *
from A
where A.did = 1988

-- returns record where card = 2157

and

select *
from B join A on A.card = B.id
where B.userid = 2157

Since you want the record where a.card = 2157 and a.card = b.id then either

Change your where statement so it is looking for b.id = 2157 or a.card = 2157
Or change your join so you're joining a.card = b.userid


Scheduled snapshots of views (without using materialized views or Oracle Golden Gate)?

You could create a function-based index on the sdo_geometry column in this view. This might help the query speed.
If you can't create an MV (have you tried?), create a table (create table as select...), then write a procedure that executes a MERGE from the view in to the table, then schedule it to execute every X minutes using dbms_job or dbms_scheduler.


A simple approach if all you care about is column names and merely identifying any tables that have the same naming scheme but have columns that don't exist in the "model" or "ideal" table (also, I kept this simple, assuming all tables are in dbo):

USE tempdb;
GO

CREATE TABLE dbo.repo_2012_08_04(a INT, b INT); -- model
CREATE TABLE dbo.repo_2012_08_05(a INT, b INT); -- same schema as model
CREATE TABLE dbo.repo_2012_08_06(a INT, c INT); -- different
CREATE TABLE dbo.repo_2012_08_07(a INT, b INT, c INT); -- different
GO

DECLARE @model SYSNAME = N'repo_2012_08_04';

SELECT t.name FROM sys.tables AS t
WHERE name LIKE N'repo[_]%'
AND name <> @model
AND EXISTS
(
  SELECT 1 FROM sys.columns AS c
    WHERE [object_id] = t.[object_id]
    AND NOT EXISTS
    (
      SELECT 1 FROM sys.columns
      WHERE [object_id] = OBJECT_ID(N'dbo.' + QUOTENAME(@model))
      AND name = c.name
    )
);
GO

DROP TABLE repo_2012_08_04, repo_2012_08_05,
           repo_2012_08_06, repo_2012_08_07;
GO

Results:

repo_2012_08_06
repo_2012_08_07


You are asking for Master --> Relay --> Slave.  This is quite possible.

Server2: STOP SLAVE
Server2: Pretend it is a Master and do the operations necessary to add its first Slave (Server3).  (Dump data Server2, copy to Server3, etc)
Server2: START SLAVE

Be sure to turn on log_slave_updates and log_bin and have different server-id.

Why do you want M->R->S?  It is a rather unusual setup.  Is it the start of something more grandiose?  I ask because there may be other tips to impart.


Do Log_Slave_Updates in MySQL 5.7 fail the replication in Linux?

ASPECT #1

Please make sure ServerA and ServerB have log_slave_updates enabled.

ASPECT #2

This setup will not break replication because every time a transaction is logged in the binary logs, the server_id of the transaction is written as well.

Let's say server_id=10 for ServerA and server_id=20 for ServerB

So, when you run this on ServerA

INSERT INTO mytable (col) VALUES (1);

The transaction will include server_id 10. When ServerB looks into its relay logs and sees server_id 10, it will compare that with its own server_id (20). Since there is no match, it is OK to execute.

ServerB records that transaction in its binary logs. ServerA will pick that transaction up from its relay logs. When ServerA looks into its relay logs and sees server_id 10, it will compare that with its own server_id (10). Since there is a match, it refuses to execute.

I discussed this before in my past answers

Mar 27, 2012 : Master-Master Replication in MySQL
Oct 15, 2012 : Will changing server_id on master break replication?


SQL Server Query Store on isolated volume

No, as of 2018/04/25, you can't choose the filegroup where Query Store data is placed. In the databases where you enable Query Store, the data goes into the default filegroup.

It was a highly upvoted Connect item, so other folks share your pain - so I would expect that to be an enhancement added in the future.

Thanks to @klitzkrieg, here are the current feedback items:

Option to store query store data in a filegroup other than PRIMARY
Create central repository for Query Store


How to avoid lock wait timeout exceed and improve MySQL InnoDB write speed

We have identified the root cause: it's the innodb_autoinc_lock_mode = 1 .

Here is the summary from the official doc:

0:  traditional lock mode, provided for backward compatibility, performance testing, and working around issues with “mixed-mode inserts”, due to possible differences in semantics.
1: consecutive lock mode: In this mode, “bulk inserts” use the special AUTO-INC table-level lock and hold it until the end of the statement. This applies to all INSERT ... SELECT, REPLACE ... SELECT, and LOAD DATA statements. Only one statement holding the AUTO-INC lock can execute at a time
2: interleaved lock mode: In this lock mode, no “INSERT-like” statements use the table-level AUTO-INC lock, and multiple statements can execute at the same time. This is the fastest and most scalable lock mode, but it is not safe when using statement-based replication or recovery scenarios when SQL statements are replayed from the binary log.

In our case, when multiple API called INSERT statements, if one API call is prolonged, that INSERT statement would hold a TABLE lock on the destination table, which then causes the timeouts.

We switch it to innodb_autoinc_lock_mode = 2, restart the server and voila.  Now we can call multiple APIs to insert into the same table without having time-outs.


Convert an interaction matrix into a ternary relation

My solution is to save the spreadsheet as a tab-delimited "matrix" file, run the following "data structure conversion" program, then import the resulting tab-delimited "relation" file into Access.

The Python 3.4 code:

import csv
source_path = 'matrix.txt'
target_path = 'relation.txt'
source_deli = '\t'
target_deli = '\t'
target_head = ("Name", "Activity", "Week")
fin = open (source_path, 'r', newline='')
fout = open (target_path, 'w', newline='')
reader = csv.DictReader (fin, delimiter=source_deli)
writer = csv.writer (fout, delimiter=target_deli)
writer.writerow (target_head)
for row in reader:
    for key in row:
        if key != "Name" and row[key]:
            target_row = (row["Name"], key, row[key])
            writer.writerow (target_row)
fin.close()
fout.close()


Why can't I drop this Oracle datafile - even though it seems empty?

Oracle here warn you from dropping datafile because it contain amount of data, to make sure the datafile is completely  empty please execute the following command

col tablespace_name format A30 heading tablespace
col file_name format A40
col tot_KB format 99,999,999
col tot_freeKB format  9,999,999
col Kb_max format  9,999,999

select f.tablespace_name
      ,f.file_name
      ,f.bytes/1024 tot_KB
      ,sum(nvl(s.bytes,0)/1024) tot_freeKB
      ,max(nvl(s.bytes,0)/1024) maxextent_freeKB
      ,( (sum(nvl(s.bytes,0)/1024)) / (f.bytes/1024) )*100 Pct_available
from   sys.dba_data_files f
      ,sys.dba_free_space s
where  f.file_id = s.file_id(+)
group by f.tablespace_name, f.file_name, f.bytes/1024
order by 1,2
;

if the datafile is completely empty, then you have one of the two option:

1) reboot the database ( sometime it help)

2) move all the data from one tablespace to another one, then drop the tablespace and rename the new one


pg_dump inserting C source code in dump file

Problem solved. Apparently there is some sort of issue with the PostgreSQL webmin module.

Dumping and restoring the DB with pg_dump in command line solved the issue. Will now backup with scripts rather that using webmin.


SQLite or CouchDB for Locational Data Mining Project?

I think the answer mainly depends on how much time do you want to spend learning new databases vs how much time to you want to spend learning machine learning.

For example PostgreSQL has lots of GIS stuff built-in which I assume could be very useful for your queries. CouchDB has many useful features with their map/reduce stuff but I find it a bit limited. If you think you're going to add many columns later on based on new algorithms you might want to look into the column-oriented databases like Apache Cassandra.

However, I would recommend splitting the problem into two parts:

Data gathering
Data analysis

The first part you seem to have a good plan for already. I would write a super simple app that would just suck in the data from twitter and put it in a table as a BLOB. Or just a file on disk.

When you have that you can go from the raw data and insert it into any kind of backend. Now you can choose backend based on the current problem (algorithm) you're working on, instead of having one solution to fit all your problems. The key point here is to extract exactly the data you need. That way you shouldn't have to consider the fact that the data is nested inside a document from Twitter as you only pick out the parts you need. If you go that way I think an RDBMS would perform very nice as they can run all kinds of queries.

Make sense?


Points that support creating a separate database for source(which is usually coming in through an API Request)

The value of having a separate source database is that if your transformation process fails, you don't have to go to the external sources to pull the data again.  You can simply fix the transformation issue and start at the transformation step, skipping the data load.

If the time that is required to pull the data from the external sources is insignificant, then it may not be worth the extra effort.  You simply have to weigh the various factors to determine what's best for the project:

How long does it take to extract the data?
If the transformation fails, how long will the original data still be available if you need to extract it again?
How quickly will you be able to address any transformation issues?
Is this a short term process, or will it be running indefinitely?  If it will be running indefinitely, you'll want to spend more time engineering so that you spend less time fixing problems, especially if someone else will be managing it eventually.

Using a source database could also speed up your testing processes since you can test the load of the source database and the transformation processes separately.


One execution of your script will produce one computer name and a list of one or more monitors. You can pass these to a SQL Server stored procedure, you just have to get the parameter types correct.

The computer name is simplest. With just this the SP definition would look like this:

create procedure dbo.MyProcedureName
    @ComputerName varchar(100)
as
    ...

I use varchar(100) as an example.  You use whatever's right for you. The list of monitors is more difficult because it may contain many values.  For this you have to use a table-valued parameter and for that you will have to define a type:

create type MonitorList AS TABLE (
    MonitorName varchar(100)
    );
GO

Now the SP looks like this:

create procedure dbo.MyProcedureName
    @ComputerName varchar(100),
    @Monitors     MonitorList    READONLY
as
    ...

Now to the body of the SP. If you use the names as the primary key in tables Computer and Monitor the INSERT statements are straightforward.  Assuming you have primary keys and foreign keys defined -- you should -- you have to insert in the correct sequence to respect these key definitions i.e. into Computer and Monitor and only then into the mapping table.

insert dbo.Computer (ComputerName)
values (@ComputerName);

The type we defined, and variables created off it, behave just like any other table:

insert dbo.Monitor (MonitorName)
select MonitorName
from @Monitors;

insert Monitor_Computer_Map (ComputerName, MonitorName)
select @ComputerName, MonitorName
from @Monitors;

If your tables have surrogate keys that use IDENTITY, however, you have more work to do.  You have to capture these identity values as they are generated in the parent tables and use them in the mapping table.  This can be done with the OUPUT clause:

declare @ComputerID table ( ComputerID int);

insert dbo.Computer (ComputerName)
output INSERTED.ID
into @ComputerID
values (@ComputerName);

Do the same thing for monitors and use the local table variables to populate the mapping table.

Of course you want to have appropriate validation, duplicate checking and error handling in the body of the SP, too.

You don't say what scripting language you use.  The documentation for it will tell you how to declare and populate stored procedure parameters for SQL Server.

Response to OP's EDIT #2:

First, a few tips.  Please post the full error message; it helps immensely with debugging.  Second, if you're using SSMS you can double-click an error and it will highlight the code in error.  Third, get in the habit of closing your statements with a semicolon.  It is not required yet but it will be soon.

If all computers have exactly one monitor then the TVP is not needed.  You are correct.  How many developers have only one monitor these days?  I've seen finance traders' stations with eight.  In these cases you do want a TVP.  Please, please, please do not be tempted to write @Serial1, @Serial2, @Serial3 ....

Your code will throw the error Must declare the scalar variable "@MonitorId". This is because of your third INSERT statement:

INSERT INTO dbo.Monitor_Computers (Monitor_Computer_Monitor, Monitor_Computer_Computer)
VALUES (@MonitorId, @ComputerId);

When you use the INSERT..VALUES syntax SQL Server demands that there can be only one value per variable.  You have provided table valued variables which could (potentially) hold many values.  What you need is the INSERT..SELECT syntax.  Yes, I know @ComputerID only has one row but it is a table and it could have many rows.  What you need is:

INSERT INTO dbo.Monitor_Computers (Monitor_Computer_Monitor, Monitor_Computer_Computer)
SELECT m.Id, c.Id
from @ComputerId AS c
cross join @MonitorId AS m;


Can't connect to a MySQL from the command line (works from MySQL Workbench)

As 'ypercube' commented, log in from both connections to tell which user is connecting. You should see differences when you run SELECT USER(),CURRENT_USER(); and SHOW GRANTS;

You are logging in but you will need to GRANT permissions (from your root account) to your @'localhost' account to gain access to your four schemas. (ref. http://dev.mysql.com/doc/refman/5.1/en/grant.html)

If this is not being used for testing, it would be adviseable to secure/drop anonymous accounts that are typically set-up with MySql if you don't intend to use them. (ref. http://dev.mysql.com/doc/refman/5.5/en/default-privileges.html)


SQL Server Index recomended but already there (DMVs)

SQL Server 2008 and 2008 R2 have known issues in the index-recommendation code that were fixed in SQL Server 2012.

Having said that, index recommendations should always be looked at carefully to see if there is (1) an existing index that might be slightly modified to handle it's current load along with the new load, and (2) if any changes can be made to the query producing the recommendation so it uses an existing index.

Paul Randal wrote about the bug in SQL Server 2008 here.


Oracle Standard Edition Monitoring Tools

There you go;

Oratify -> I think, This is the best.
Solarwinds -> It's fine but I don't know about Standard Edition Support
MyOra -> We were using about 5 years ago but if you have free time you can try it :)


What do the HT* wait types mean? (HTREPARTITION, HTDELETE, ...)

Community Wiki answer generated from a comment on the question by ypercube

From an MSDN Forums thread:

  HTDELETE

  SQL Server 2014 now uses one shared hash table instead of per-thread copy.
  This provides the benefit of significantly lowering the amount of memory
  required to persist the hash table but, as you can imagine, the multiple
  threads depending on that single copy of the hash table must synchronize with
  each other before, for example, deallocating the hash table. To do so, those
  threads wait on the HTDELETE (Hash Table DELETE) wait type.

(This only applies to hash operators that execute in batch mode)


How to rebuild entire MySQL data after server re install?

MySQL replication works with mixed hardware architecture
http://dev.mysql.com/doc/refman/4.1/en/replication-faq.html
By default all MySQL databases, users, previleges, settings etc.. unless you specify

Replicate_Ignore_DB
Replicate_Ignore_Table

option while starting a slave server.


How to troubleshoot hung queries in PostgreSQL RDS?

The most important information in addition to the connection state of the hung queries would be whether they are in any particular wait event.

You can get that information by running “SELECT state, wait_event_type, wait_event FROM pg_stat_activity” (you may need to collect this in a cron job to get the data over time)

Note that RDS also shows this information in “Performance Insights”, if you have it enabled.

Since you are on an older Postgres patch release, I would also investigate an upgrade of the minor version to see if that fixes the issue you are seeing.


Foreign Keys with ON DELETE SET NULL are still deleted when TRUNCATE CASCADE is called on the foreign table in Postgres

If I understand the documentation properly:

https://www.postgresql.org/docs/current/sql-truncate.html

TRUNCATE CASCADE truncates every table that has a foreign key relationship to the table in common, regardless of what action is specified for the foreign key. Example:

create table parent
( x int not null primary key);

create table child
( y int not null primary key
, x int not null references parent(x)
                 on delete restrict
                 on update restrict
);

insert into parent (x) values (1),(2),(3);
insert into child (y,x) values (1,1),(3,2);

truncate parent cascade;

select * from child;

There are no results to be displayed.

Is there something in particular that prevents you from:

delete from parent;

?


Backup cut-off time for Full, Differential, Log, File, Partial backups

See this related question for the answer regarding full backups. The definitive technical source referenced there is a 2009 article by Paul Randal (who was a member of the Microsoft SQL dev team):

  A full database backup provides a complete copy of the database and provides a single point-in-time to which the database can be restored. Even though it may take many hours for the backup process to run, you can still only restore the backup to a single point (effectively at the end of the backup, but I'll discuss exactly what that point is later in this article). A full backup does not allow recovery to any point in time while the backup was running.

If you want to pick apart what he means by "effectively" here, you have to understand the steps a full backup goes through:

Checkpoint the database and note the current log sequence number
Start reading the data files
Stop reading the data files, and note the current log sequence number
Read enough of the log file to capture transactions completed between steps 1 and 3.

A full backup contains the proper data to restore the database to the point between steps 3 and 4. And since step 4 typically completes quickly (unless there was tremendously high activity during the backup), this is effectively the end of the backup.

Regarding Differential backups, Randal (in the same article linked above) says:

  A differential backup performs the same operations as a full backup, but only contains all the data that has changed or been added since the previous full backup.

He then goes through the detail of how exactly SQL identifies which pages have changed since the last full, but that only changes how steps 2-3 above operate, it doesn't change anything else. I have to assume that implies the same answer, that restoring a full then a differential backup will restore to a point very near the end of the differential backup, with the caveats we've described above.

I'll look for an authoritative source about log backups.

Sorry, I don't have any detail about file or partition backups, I would guess they operate similar to a full, just with a different set of data pages.


Instead of

  Open each affected objects (i.e. ALTER SP) and execute it again so
  that the reference to table type will be updated

you can just find all the affected objects and refresh them using sp_refreshsqlmodule.

Here is the code to find the modules:

select object_name(referencing_id)
from sys.sql_expression_dependencies
where referenced_class_desc = 'TYPE' and referenced_entity_name =  'your_table_type_name';

To do refresh of all this objects you can simply generate the commands like this:

select 'sp_refreshsqlmodule ' + quotename( object_name(referencing_id), '''')
from sys.sql_expression_dependencies
where referenced_class_desc = 'TYPE' and referenced_entity_name =  'your_table_type_name';

Make the output to text instead of grid and copy + paste generated rows to SSMS and execute them.


Odds are the the account with which the Database Mail profile is logging into the SMTP server doesn't have rights within Exchange to send to the distribution list.  You need to work with your exchange admin to see what's going on within the Exchange server.  There are tracing tools within Exchange they can use to see exactly what's going on.


MySQL replication - slave hangs during ALTER table or BULK update - v5.1.71

ALTER TABLE operations before 5.6 always required a full write table lock (which means the whole table could be read, but not written). Bulk updates depend on the kind of operations and the ENGINE used (for example, LOAD DATA will lock the whole table on MyISAM, but it will only create row-based locks on InnoDB tables.

If you are in a replication environment, executing an alter table before 5.6 will:

Lock the table on the master
Alter the structure and insert the original data of the table (on the master) using a temporary table, which may take a long time. This is not true for all operations- some name-only changes or secondary indexes manipulation on the InnoDB plugin may be faster and may not require a full table copy. While this is happening, replication is up and running.
Unlock the tables and log the statement (using STATEMENT binary log format) to the binary log on the master
The log is transmitted to the slave
The slave will apply the changes as in the same way it is done on the steps 1-3, with a big difference: the slave will not apply other changes while this is happening because replication is single threaded (so it does apply the changes in the same order than originally)

This 5th step is problematic (and probably the reason why you think that the server is hanged, because in the master, other actions could be applied in parallel to other objects, but not on the slave.

In case of bulk updates, a lock may or not be involved, but you have an additional problem: logging the changes though the binary log is usually very slow (slower than its direct application).

Then there is the issue with stopping a slave: when you try to stop slave or restart the server, any other ongoing operation would be killed, but application of slaves is allowed to finish to avoid replication issues (the ones you mention on your bug report). You can actually kill the threads, but it may only make things worse, breaking your replication on non-transactional tables (or non-transactional replication control files) or falling back to a rollback, that could take even more time that a commit.

Does 5.6 solve anything of this? Well, in some cases, it may help reducing the problems:

it introduces transactional on-table replication control to minimize problems when the slave crashes.
it allows certain ALTER TABLEs on InnoDB tables to run in a hot way, allowing concurrent writes
It has multithreaded replication, but it is only useful for multi-tenant databases (one thread per schema). Real multithreading will be available on 5.7.

So I would say that it will not provide you 100% solution to your problem.

However, what I would recommend you is to perform schema changes and data loads in a slave -> master order (assuming that they are changes that are backwards compatible) and editing directly every node instead of using the binary log.

For example, you would execute this:

SET SQL_LOG_BIN = 0
-- Blocking operation, like ALTER or LOAD DATA, etc.
SET SQL_LOG_BIN = 1

On the slave first, and then on the master, so that you write those independently. To minimize downtime, you can do a switchover between servers so that at any time, the active node is not blocked for reading nor writing.

Of course, in order for this to be consistent, you need to make sure the changes are compatible (adding a column is ok, deleting one is not), and that your application does not use the new version until both servers have been migrated. Basically, as locking mechanisms are avoided, you are in charge of making sure that your changes are consistent.


How to join two tables with different numbers of rows

When joining you join one key to another x=y but you seem to want to join x=y-1 instead. In your case you want to join 1 with 0 and 2 with 1. This is not a good practice. When joining you always you should join keys with eachother without chaning them.

You should think about what you really want as a result because it is not a logical join to do. But to genereate the result you want you can do like this:

select a.*, b.* from t1 a LEFT OUTER JOIN t2 b on a.rnum-1 = b.rnum

DB Fiddle

This is a good reference image for future use:


How to convert mysql database charset to utf-8

http://dev.mysql.com/doc/refman/5.1/en/alter-table.html

  If you want to change the table default character set and all
  character columns (CHAR, VARCHAR, TEXT) to a new character set, use a
  statement like this:

  ALTER TABLE tbl_name CONVERT TO CHARACTER SET charset_name;

Follow the link for more details.

Be sure to do this in a test enviornment first.  You probably wont run into many issues going from latin1 => utf8 but in general you might run into collation "weirdness" when converting charsets.  This is especially true if you have any kind of unique constraints on char-ish columns.

Also keep in mind if you do have any char or varchar columns indexed moving to utf-8 will triple the amount of memory needed


Changing passphrase for EncryptByPassPhrase

You'll need to decrypt the data using the existing password then encrypt the value using the new password.


You need one row per product. So GROUP BY needed. And the query will look like

SELECT product_id, (expression for availability)
FROM sourcetable
GROUP BY product_id

You have 3 general variants for the amount - 0, 1 or >1 (if some record is absent we must assume its amount value is 0). For to formalize we will substract 1 and get the sign (it will compact the amount into 3 values of -1, 0 or +1). For store_id != 7 we can get max value only and ignore all another. So source data can be prepared in CTE, and final query will be

WITH cte AS ( SELECT product_id,
                     SIGN(MAX(CASE WHEN store_id != 7
                                   THEN available_count
                                   ELSE 0 END) -1) stock,
                     SIGN(MAX(CASE WHEN store_id = 7
                              THEN available_count
                              ELSE 0 END) -1) eshop
              FROM sourcetable
              GROUP BY product_id )
SELECT product_id,
       CASE stock * 3 + eshop
           WHEN -4 THEN ...
           WHEN -3 THEN ...
           ...
           WHEN 3 THEN ...
           WHEN 4 THEN ...
       END AS availability
FROM cte

Test this. Fill the variants in final CASE by yourself, please...


There are 7 database to backup and 7 *.bak files to copy, it is huge 330 GB to back up and transfer to NAS everyday and it is increasing up to 5 gb everyday.

That is the size of the data you have. Either you need to back it up, or you don't. Are all of these databases in simple recovery mode? Should they be? One thing you could consider is taking full + diff/log backups and rotate the schedule. Database 1 gets a full backup on Sunday, and diff backups once a day for the rest of the week, and log backups throughout the day on each day. Database 2 gets a full backup on Monday, and diff backups once a day for the rest of the week, and log backups throughout the day on each day. And so on. This way you spread out the pain of the bigger backups throughout the week.

But as your data grows, you need to grow your capacity along with it, whether that's a better network pipe, a more reliable/bigger NAS, or both.

One thing to note is that if your backup file is getting bigger and bigger all the time, a very common issue is writing backups to the same file without using WITH INIT. We just talked about this here.

  Some times backups does not transfered correctly

This does not seem like a database issue, sounds like infrastructure. Maybe you need a better NAS or a more reliable network or better "backup software" or just simply a better, more resilient copy method (e.g. RoboCopy).

  it takes a lot of time to backup and transfer it to Nas device

Let's split those out.

If the backup takes a long time, again, consider whether you need to be taking full backups every day. This seems atypical unless you really could not care less about point in time recovery. If you do, and use full recovery model, this should help because (a) you won't have to perform a full, standalone backup every night, and (b) suspect the size of the log will be much better managed as well.
If the network transfer takes a long time, that is likely a symptom of your network and not anything you're going to be able to fix with code or changes to your backup routine. But this should be a background process anyway. Does it matter if it takes 10 minutes or 12 minutes? Is someone actively waiting on this copy to complete?


Difference between the following postgresql indexes

Check for table and index sizes. Your first index uses 3 columns and its size is probably very big so optimizer chooses full table scan.
Index scan vs full table scan also depends on random_page_cost (default value 4).
If random_page_cost is set lower optimizer will chose index scans over full table scans.


Sure.

Enable auditing as:

alter system set audit_trail=db,extended scope=spfile;

Restart the database for the above to take effect.

Then enable auditing on the table:

audit insert on schema.table by access;

Then view the audit records in DBA_AUDIT_TRAIL. This gives you b. and c.. Using SQL_TEXT, you can find the SQL in V$SQL, and in V$SQL, the PROGRAM_ID (=OBJECT_ID in DBA_OBJECTS) and PROGRAM_LINE# columns tell you where that statement came from.


Debit Credit Balance calculating from previous records with Pagination

As you have also tagged it with Postgres:

select id,
       "in",
       "out",
       sum("in") over (order by id) - sum("out") over (order by id) as balance
from data
order by id;

The above is not Postgres specific, it's standard ANSI SQL using window functions.

Online example: http://rextester.com/ENBLS73027

To add pagination, use row_number()

select id, "in", "out", balance
from (
  select id,
         "in",
         "out",
         sum("in") over (order by id) - sum("out") over (order by id) as balance,
         row_Number() over (order by id) as rn
  from data
) t
where rn between 3 and 6 -- rows 3,4,5 and 6
order by id;


Database_Id for our database in sys.databases does not match that of DB_ID ( '<our database' )

Azure SQL DB is different !

The database_id column from sys.databases (dont use sysdatabases <-- its deprecated) will remain the same for the database.

The DB_ID() is a function and the value will change if the Azure DB is failed over.

Best is to use name <-- database name column.

People have reported that here and here.

  One shouldn't rely on database id / db_id builtin in azure as it changes whenever database moves to a different sql instance (because of the database failover). Use name instead of the id. Also please note that the db_id can potentially be different than in the sys.databases


need suggestions to improve view performance

Views are typically not implemented for performance. And while you currently cannot implement explicit indexed views (which are just views that SQL Server maintains for you), you can certainly maintain facts manually yourself.

For example, you mention that you currently calculate "whether someone is dead or not" using three CTEs and a CASE expression (sorry, to be pedantic, it's not a statement).

Instead of referencing this set of CTEs every time the view is accessed, why not put that fact in a table (potentially along with other facts that have to be calculated per user), and calculate that periodically in the background? So maybe every 5 minutes (that is just a SWAG, you'll have to determine what's appropriate), you run a SQL Server Agent job that re-populates the table based on what it currently knows is the truth. Now the view just has to reference the table that is the output of that script, instead of calculating it over and over again while the users wait. So for example:

CREATE TABLE dbo.PersonProperties
(
  PersonID INT PRIMARY KEY REFERENCES dbo.Persons(PersonID),
  IsDead BIT NOT NULL DEFAULT 0
);

Now the job can simply merge that table with the results of the CTE, and then the view can include a reference to that table which simply pulls the BIT column along with a join on the PK. This should be MUCH less expensive at query time that re-evaluating all of that logic every time.

To minimize blocking (e.g. when users are accessing the view at the same time the job is running), you can implement what I call "schema switch-a-roo" and which I blogged about here:

http://www.sqlperformance.com/2012/08/t-sql-queries/t-sql-tuesday-schema-switch-a-roo

So instead of locking resources on the expensive query throughout the operation, the only blocking that happens is when the metadata switch actually takes place.

This works as long as you can afford some brief periods where the data is not accurate. You can tighten up that window to be pretty narrow, but there is always a chance that a person will die in between and for a brief moment a query will return that they are still alive. If you can't afford this, then you make it a part of the process that first introduces that fact to the database to make sure the CTE reflects that immediately and the new table also reflects it immediately.

Still not good enough? The flag a user as "dirty" the second a change for them comes in. The view can union or left join with the stale data for users that are "clean" and go after live data only for the users that are "dirty."


The query itself only takes 82 milliseconds (0.082 seconds).

The reported 26 seconds are either caused by the network or by the application (or SQL client) processing and displaying the result. As far as I can tell, it's highly unlikely that it's the network as the 46375 rows add up to roughly 30KB of transmitted data.


Do we have logging feature for SQL AUDIT like we have it for transaction and error logs in SQL server 2008?

There's two forms to audit a SQL Server

The SQL Profile:

https://msdn.microsoft.com/en-us/library/ff650699.aspx

And DDL Trigger's

https://msdn.microsoft.com/en-us/library/ms189799.aspx


Where to put standby control file in Physical standby database?

You can copy the same standby file controfile to all the locations and use it.
All control_files have the same content.

If you are creating new standby, I would suggest to follow below method (which uses active duplication): https://dbaclass.com/article/how-to-create-physical-standby-database/


Database defragmentation and autogrowth settings

if any table has page count more [than] 50 [pages]

That is still extremely small.  I typically don't take fragmentation into account for any index with less than 1000 pages.

  and avg_fragmentation_in_percent > 30 then the index is REBUILD

That's a good threshold for rebuilds, but I'd also consider reorganizing indexes with fragmentation in the range of 5% to 30%.

  If the database log size>2 MB, then the recovery mode is made as simple, and it is shrinked, and the recovery mode is set back to FULL

Why are you doing this?  Why is it a problem if a log file is greater than 2 MB?  That's extremely small.  Not to mention, when you switch to SIMPLE, shrink, and then back to FULL recovery you are breaking the log chain.  You could get yourself into serious trouble for point in time recovery.  Size your log files appropriately, and allow for autogrowth only in the case of emergency.  Shrinking a database file should only be done in extremely corner cases, and by no means part of a routine maintenance schedule.

  But we found that autogrowth events are resource incentive and it should not happen repeatedly

Correct.  That is where sizing comes into play.  Monitor and get alerted when a certain percentage of space is being used.  That way you can plan your manual file growth operations for a maintenance window to minimize end-user impact.

  and 10% for ldf file which is default value while creating new database

Even though a percentage file growth is default, I recommend against it.  You don't want to have a variable amount of file growth.  If you must fall back on autogrowth, then you want to know exactly how much the file will grow by.  Not to mention, with growth of a log file you will have a variable amount of virtual log files (VLF) with the additional space.  You want to smartly plan this, and this can be done through a fixed amount of file growth.

  Should i set autogroth so that it happens only once a day,week or month etc

Set autogrowth for a fixed amount of space (not percentage), and only you can determine what is a good amount.  Too small, you will have frequent autogrowth with minimal end user impact, but happening often.  Autogrowth set too high, then you'll have less frequent growths but when it does happen the impact will be for a longer duration.

My recommendation?  Monitor the space used in your database files.  When it hits a certain percentage (say, 80% space used) you should be alerted.  And then schedule a manual growth during a maintenance window.


powershell to connect to SQL and display a friendly message when it cannot connect

You need to try/catch the exception.

$OutputFile="C:\output.txt"
$HostName="MyServer"

try
{
    $con="server=$HostName;database=master;Integrated security=sspi"
    $da=New-Object System.Data.SqlClient.SqlDataAdapter ("

            declare @ServerRestartDate datetime
            set @ServerRestartDate = (select crdate from master..sysdatabases where dbid=2)
            if(@ServerRestartDate>GETDATE()-1)
                print @@servername +' was restarted in last 24 hours. Restart date/time = '+cast(@ServerRestartDate as varchar(20))

                                        ",$con)
    $dt=New-Object System.Data.DataTable
    $da.fill($dt)|Out-Null
    $svr
    $dt|Format-table -AutoSize |Out-File $OutputFile -Append
}
catch [Exception]
{
    // Do something with the exception
    Write-Host $_.Exception.ToString()
}


Ensure that only one record has a specific value

That can't be performed with single query. Therefore you have to create stored routine like that:

CREATE PROCEDURE `start_job`( IN `pid` INT, IN `state` varchar )
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
BEGIN
   UPDATE *
     FROM jobs AS w
      SET w.status = "idle"
    WHERE w.status = "running"
   ;
   INSERT INTO jobs (id, status)
   VALUES (pid, state)
       ON DUPLICATE KEY UPDATE
          status = state
   ;
END


VARCHAR memory buffer appears to be breaking in ChineseTaiwan Collation

You need to ensure the strings (stored procedure parameters) are sent NVARCHAR

DECLARE @CollationTest table (
     ID int NOT NULL,
     KoreanV varchar(2048) COLLATE Korean_Wansung_CI_AS NULL ,
     KoreanNV nvarchar(2048) COLLATE Korean_Wansung_CI_AS NULL ,
     ChineseTaiwanV varchar(2048) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
     ChineseTaiwanNV nvarchar(2048) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL
    )

INSERT @CollationTest VALUES (1,
    '한국어',
    N'한국어',
    '韓國人學台',
    N'韓國人學台'
)

INSERT @CollationTest VALUES (2,
    N'한국어',
    N'한국어',
    N'韓國人學台',
    N'韓國人學台'
)
SELECT * FROM @CollationTest


Query for a single unique record takes about 1.5 seconds

I've seen some options in .NET be different than the default SSMS options. Specifically, ARITHABORT was ON in SSMS, but in .NET it was OFF (the default value in the DB was off, where I assume .NET was pulling this from). This caused SQL Server to choose a vastly different (and terrible) plan in the .NET code.

Can you verify the options (ANSI_NULLS, QUOTE_IDENTIFIER, ARITHABORT, etc.) are consistent? My guess is that they are not.

See Slow in the Application, Fast in SSMS? by Erland Sommarskog for more information.


Access the database by different users simultaneously

Since you mention that several users are working fine for a while, you have the database configured for multi-user Shared access.

However, apparently someone on "machine no." is able to acquire Exclusive control of the database. The errors you are getting are the fallout from the Open Exclusive and likely does not indicate any corruption.

This suggests that you also need to configure the security for the database to deny your users the Open Exclusive permission.

There once was help on this at this at a now defunct link. For what it is worth, you can examine:  http://traycare.com/Help/NtwrkOps.htm  and see if it helps you.

EDIT: Note that changes to the database structure (Forms, Views, Tables, etc) require Open Exclusive and should be scheduled for a maintenance window so as to avoid unnecessarily interfering with users of the database.


If you move to SQL Server 2012 Management Studio (and no reason not to, now that it's free and you can still manage the same set of down-level servers that 2008 R2 lets you manage), you can simply delete old servers right from the connection dialog using the standard Delete key.

Deleting Old server names from "Connect to Server" dialog in SSMS


SQL Full Backup job is taking too long to complete

I found last_wait_type is ASYNC_IO_COMPLETION for this backup job. We
  have sufficient disk space available.

This blogpost from Paul Randal gives you most of the information regarding this wait stat

  ...The official definition of ASYNC_IO_COMPLETION is ‘Occurs when a task
  is waiting for I/Os to finish....

  ...Long-duration ASYNC_IO_COMPLETION waits commonly are from data backups....

A question similar to yours can be found on that same blog.
In his case a backup of a small database (1,5GB)  was taking over 15 minutes.

Q

  ...I’m facing a high level of ASYNC_IO_COMPLETION into one of my environments what is resulting in backups of small databases (around 1,5GB) to take more than 15 minutes. I have a feeling that it is related to low performance on the I/O
  subsystem. But I’m not 100% confidente to go ahead on this diagnostic
  to ask for analysis or changes on that.

A

  Yup – that’s what it is. Try doing a backup to nul, as that will give
  you time taken to read from the data files. The difference is the time
  being taken to write out the backup files – there’s your ammo.

This is the command to do that:

BACKUP DATABASE DatabaseName TO DISK = 'NUL'

And compare this with the  backup time you mentioned previously.

Keep in mind that this will still start a huge amount of I/O operations on your server.

  And the backup job is also showing suspended state

Suspended state simply means that it is waiting on resources, I/O based in your case.

Other improvements

Check what else is running on the instance, any maintenance plans such as index rebuilds, statistic updates, integrity checks?

You could start logging queries with sp_whoisactive and perhaps logging it in a table.

Striping your backups accross multiple files can help in reducing backup time. More on that here.
My go to is 4 files but you could test different settings.

Since you are on sql server 2012, if you are not using TDE you could leverage backup compression as well.

More information on making backups go faster can be found here


how to restrict usage of datafile in Oracle

First, I'd check that it is not specified as the default TS for any user:
select default_tablespace,
       username
from dba_users
order by default_tablespace,
         username
;

Then double-check that nothing has crept back in;
select owner,
       segment_name,
       segment_type
from dba_segments
where tablespace_name='nameoftswearelookingat';

-- Edit - add comments about quota
One other thing to check and that is user quota.  If a user has available quota on a TS, they can create segments in it even if it is not their default TS.
select *
from dba_ts_quotas
where tablespace_name='nameofyourtablespace';


By Mistake Deleted AlwaysOn Availability Database from live Production Server

It is not possible to delete a database that currently resides in an availability group. It is also not possible to restore over top of it while it remains in the availability group. Thus, this database was not in an availability group to begin with.

What you are describing is not possible as you will get an error if the database is involved in Always On. You'll receive the following error:

  Msg 3752, Level 16, State 1, Line 1 The database '<Database name>' is
  currently joined to an availability group. Before you can drop the
  database, you need to remove it from the availability group. You have
  to SET HADR OFF before issuing a drop database.


The password is encrypted via a one-way algorithm.  You cannot discover the length or contents (numbers, punctuation, etc) of passwords once they are stored.

Password rules need to be enforced when they are first created, even before touching MySQL.

If you want expiration rules that are easily configured and maintained, upgrade to 5.7.  (Anyway 5.1 is antique by now!)


Why MSDB transaction log file grew while MSDB was in Simple recovery mode?

For me, large growths in MSDB are from adding and deleting the table entries within the backupset tables. Do you have a history purge or anything running?


Data-Driven Subscription - how to iterate through all parameter values?

You need to put those 20 hard-coded values into a table, instead, and then you can refer to that in your data-driven subscription. That will also make it easier to maintain because you can now just add or remove records from that table instead of having to change and re-deploy the entire report.


Applying audit policy on multiple auditing subject or object

Whilst the audit policy created by CREATE AUDIT POLICY is database-wide, it is applied to individual objects or roles by the AUDIT statement. For example, to audit statement executions (successful or otherwise) against a particular table:

CREATE AUDIT POLICY allexec CATEGORIES EXECUTE STATUS BOTH;
COMMIT;
AUDIT TABLE mytable USING POLICY allexec;
COMMIT;

To audit statements executed by a particular role:

AUDIT ROLE somerole USING POLICY allexec;
COMMIT;

The reason for all the COMMITs is that, as the docs note, "[a]n AUDIT-exclusive SQL statement must be followed by a COMMIT or ROLLBACK statement...". See audit policies for more information.


Is a surrogate key better than a natural key in this case

Email is a particularly bad choice for any PK whether composite or single.  See my answer on this question on Stack Overflow for why:

https://stackoverflow.com/questions/3804108/is-email-address-a-bad-primary-key/3804174#3804174


Where clause returns null, but need to return 0

I think you need a LEFT JOIN or an inline subquery. The FROM b is also missing.

For the NULL to be converted to 0, you need the equivalent of COALESCE() or ISNULL(), which is the Nz() function in Access:

SELECT a.partNumber,
       Nz( (SELECT SUM(b.purchased)
            FROM b
            WHERE b.partID = a.partID
           ), 0) AS sum_purchased
FROM a ;

Assuming that table's primary (or unique) key is partID, you can have the same results with a LEFT JOIN:

SELECT a.partNumber,
       Nz(SUM(b.purchased), 0) AS sum_purchased
FROM a
  LEFT JOIN b
    ON b.partID = a.partID
GROUP BY a.partID, a.partNumber ;


Gaps and islands: client solution vs T-SQL query

The following C# code solves the problem:

    var connString =
        "Initial Catalog=MyDb;Data Source=MyServer;Integrated Security=SSPI;Application Name=Benchmarks;";

    var stopWatch = new Stopwatch();
    stopWatch.Start();

    using (var conn = new SqlConnection(connString))
    {
        conn.Open();
        var command = conn.CreateCommand();
        command.CommandText = "dbo.GetAllTaskEvents";
        command.CommandType = CommandType.StoredProcedure;
        var gaps = new List<string>();
        using (var dr = command.ExecuteReader())
        {
            var currentEvents = 0;
            var gapStart = new DateTime();
            var gapStarted = false;
            while (dr.Read())
            {
                var change = dr.GetInt32(1);
                if (change == -1 && currentEvents == 1)
                {
                    gapStart = dr.GetDateTime(0);
                    gapStarted = true;
                }
                else if (change == 1 && currentEvents == 0 && gapStarted)
                {
                    gaps.Add(string.Format("({0},{1})", gapStart, dr.GetDateTime(0)));
                    gapStarted = false;
                }
                currentEvents += change;
            }
        }
        File.WriteAllLines(@"C:\Temp\Gaps.txt", gaps);
    }

    stopWatch.Stop();
    System.Console.WriteLine("Elapsed: " + stopWatch.Elapsed);

This code invokes this stored procedure:

CREATE PROCEDURE dbo.GetAllTaskEvents
AS
  BEGIN ;
    SELECT  EventTime ,
            Change
    FROM    ( SELECT  StartedAt AS EventTime ,
                      1 AS Change
              FROM    dbo.Tasks
              UNION ALL
              SELECT  FinishedAt AS EventTime ,
                      -1 AS Change
              FROM    dbo.Tasks
            ) AS TaskEvents
    ORDER BY EventTime, Change DESC ;
  END ;
GO

It finds and prints one gap in 2M intervals in the following times, warm cache:

1 gap: Elapsed: 00:00:01.4852029 00:00:01.4444307 00:00:01.4644152

It finds and prints 2M-1 gaps in 2M intervals in the following times, warm cache:

2M-1 gaps Elapsed: 00:00:08.8576637 00:00:08.9123053 00:00:09.0372344 00:00:08.8545477

This is a very simple solution - it took me 10 minutes to develop. A recent college graduate can come up with it. On the database side, execution plan is a trivial merge join which uses very little CPU and memory.

Edit: to be realistic, I am running client and server on separate boxes.


The principal difficulty here is probably that the alternative value of MemoText you want to use in case the fieldnum=142 value is null is in a different row. It would be much easier if it was just a different column of the same row. That way you could use the pattern suggested by Manu – I mean specifically this part:

set <field_to_update> = ISNULL(<field142>, <field17>)

The ISNULL function will evaluate to <field142> if that value is not null, otherwise the function will evaluate to <field17>.

So is it possible to arrange two values from different rows on the same row? Yes, there are at least two ways to do so.

First, you could replace the eqanswer reference with a derived table that groups eqanswer rows by entitynum and uses conditional aggregation to present field 142 and field 17 values on the same row, like this:

SELECT
  entitynum,
  MAX(CASE fieldnum WHEN 142 THEN MemoText END) AS MemoText142,
  MAX(CASE fieldnum WHEN 17  THEN MemoText END) AS MemoText17
FROM
  eqanswer
WHERE
  entityrole = 'DEFENDANT'
  AND fieldnum IN (142, 17)
GROUP BY
  entitynum

Now you can use the above query in the UPDATE statement like this:

UPDATE
  xrji_:Usernum
SET
  DefendantAddress = TRIM(ISNULL(eqa.MemoText142, eqa.MemoText17))
FROM
  (
    SELECT
      entitynum,
      MAX(CASE fieldnum WHEN 142 THEN MemoText END) AS MemoText142,
      MAX(CASE fieldnum WHEN 17  THEN MemoText END) AS MemoText17
    FROM
      eqanswer
    WHERE
      entityrole = 'DEFENDANT'
      AND fieldnum IN (142, 17)
    GROUP BY
      entitynum
  ) AS eqa
WHERE
  eqa.entitynum = xrji_:Usernum.DefendantEntityNum
;

The other method would require joining the eqanswer table to xrji_:Usernum twice: once to get the field 142 MemoText and once again to get the field 17 MemoText. For this it would be more convenient to rewrite UPDATE to use an explicit join. The complete statement would look something like this:

UPDATE
  xun
SET
  DefendantAddress = TRIM(ISNULL(eqa142.MemoText, eqa17.MemoText))
FROM
  xrji_:Usernum AS xun
  LEFT JOIN eqanswer AS eqa142 ON eqa142.entitynum = xun.DefendantEntityNum
    AND eqa142.entityrole = 'DEFENDANT'
    AND eqa142.fieldnum = 142
  LEFT JOIN eqanswer AS eqa17 ON eqa17.entitynum = xun.DefendantEntityNum
    AND eqa17.entityrole = 'DEFENDANT'
    AND eqa17.fieldnum = 17
WHERE
  eqa142.entitynum IS NOT NULL
  OR eqa17.entitynum IS NOT NULL
;

You can see that this UPDATE statement uses the same ISNULL pattern as the previous UPDATE does. The difference is only in how the MemoText values from different rows manage to get on the same row. This time they simply come from different, independently joined subsets of eqanswer.

The query uses outer joins instead of inner joins because of the assumption that field 142 or field 17 may be absent for any given entitynum. The query still checks that at least one of the two rows is present – otherwise the corresponding xrji_:Usernum row is not updated at all. If you still would like to update such rows (with a null, apparently), just remove the WHERE clause.


Will a covering index save a table hit if only the commoly-used length of a `text` field is indexed?

It depends.

Prefix indexing INDEX baz(800) is rarely useful.
MyISAM and InnoDB work dramatically differently in the area you are in.
In InnoDB, if id is the PRIMARY KEY, it is almost useless to have an index starting with id.  This is because the PK is clustered with the data.  For MyISAM, the PK is a separate BTree, so the analysis is quite different.
If you are using InnoDB, which version, etc, is the table?  Antelope, ... COMPRESSED, ...?
Adding extra indexes adds more stuff to cache, thereby possibly slowing down other queries by bumping things out of cache.  Since your 'optimization', if it works, is not likely to save more than one disk hit, you may not actually be speeding up the system any.


Cannot initialize the data source object of OLE DB provider “Microsoft.ACE.OLEDB.12.0” for linked server

I fixed the problem by disabling UAC within the control panel. I am unsure what changes this made, but it might help others!


MySQL replication - some values are not being replicated

Are you aware of http://www.mysqlperformanceblog.com/2009/05/14/why-mysqls-binlog-do-db-option-is-dangerous/ ?

Perhaps some of the missing updates are being run with the test database as the current database?

It is less error-prone if you use the replicate-wild-* options. In your case

    replicate-wild-ignore-table = mysql.*
    replicate-wild-ignore-table = test.*


What's the benefit of CROSS JOIN vs INNER JOIN in recursive CTE queries?

Independent of the CTE context, comma-separated items in the FROM list are equivalent to the same with CROSS JOIN replacing the commas. And [INNER] JOIN is exactly the same as CROSS JOIN with a WHERE clause. The manual:

  FROM T1 CROSS JOIN T2 is equivalent to FROM T1 INNER JOIN T2 ON TRUE (see below). It is also equivalent to FROM T1, T2.

But there is a subtle difference between comma and explicit JOIN syntax. Comma separates more strictly. See:

What does [FROM x, y] mean in Postgres?


The problem is in your application of step 2:

  If none of the relation schemas in Ri contains a candidate key of R0 Create new relation schema Rj contains attributes form a key of R0

This means that you must add a new relation (but only one!) that contains one of the original candidate keys (any one!).

So correct decompositions in 3NF are {R1, R3, R4} or {R1, R3, R5}. You can chose one of the two as result of the question (and this means yes, the synthesis algorithm can produce different decompositions).

Finally we can note that this step is used to guarantee the lossless-join property (while the dependency preservation is guaranteed by the previous steps, since all the decompositions are considered). In fact a theorem exists that says that if a decomposition preserves the dependencies and has at least a relation which contain one of the original candidate keys, than this decomposition preserves the data (i.e. is a lossless-join decomposition).


set default datatype = char (instead of varchar)

The default column data type in the SQL Server Management Studio table designer is not currently configurable.  If this feature is important to you, consider submitting a suggestion on Connect.  Be sure to specify why the feature has value to you and your organization (e.g. enhanced productivity).

The default type is configurable in the Visual Studio IDE, though.  In VS 2015, the option is available under Options-->Database Tools-->Table and Database Designers-->Column Options.


I would expect the one with the most recent datetime stamp to be the one that is in use.


Fiscal Year dynamic function (Oracle SQL)

create or replace function fiscal_year_start_date(p_date date) return date
as
  pragma udf;
begin
  return add_months(trunc(add_months(p_date,3),'YYYY'), -3);
end;
/

alter session set nls_date_format='YYYY-MM-DD';

select fiscal_year_start_date(date'2016-01-01') from dual;

FISCAL_YEA
----------
2015-10-01

pragma_udf is optional, it is a 12c feature that boosts performance of such user-defined functions a bit when used in plain SQL (but may degrade in PL/SQL).


How to get a list/result of best selling items

Assuming SQL Server, I might use:

SELECT TOP(5) ProductID, SUM(Quantity) AS TotalQuantity
FROM order_items
GROUP BY ProductID
ORDER BY SUM(Quantity) DESC;

This returns the top 5 best-selling Products.

Since SUM(Quantity) is also included in the SELECT clause, you can reference its alias in ORDER BY instead of repeating the expression:

...
ORDER BY TotalQuantity DESC;