Incorrect syntax generating XML I am trying to run below code in SQL server 2000 SP4 but getting error.

Declare @Body varchar(8000);
Declare @TableHead varchar(8000);
Declare @TableTail varchar(8000);

Set NoCount On;
Set @TableTail = '
';
Set @TableHead = '' +
            '' +
            '' +
            '
' +
            '
Server Name	' +
            'Product	' +
            'Provider	' +
            'Data Source	' +
            'Is Linked?
';

Select @Body = (Select
        name As [TD],
        product As [TD],
        provider As [TD],
        data_source As [TD align=center],
        is_linked As [TD align=center]
    From sys.servers
    Order By is_linked, name
    For XML Raw('tr'), Elements);

Set @Body = Replace(@Body, '_x003D_', '=');
Set @Body = Replace(@Body, '_x0020_', space(1));

Select @TableHead + @Body + @TableTail;

Error:

Msg 156, Level 15, State 1
Incorrect syntax near the keyword 'For'.

I took this code from here:

http://www.sqlsoldier.com/wp/sqlserver/buildinghtmlemailswithsqlserverandxml

and am using it as a reference to build a new report. I have a mix of SQL Server 2000, 2005, 2008, 2012 instances in my environment. This code works on other servers but not in 2000 SP4.

Please help me fix this.

According to this MSDN article for using FOR XML in SQL Server 2000, "ELEMENTS Specifies that the columns are returned as subelements. Otherwise, they are mapped to XML attributes. This option is supported in AUTO mode only." So, your usage of ELEMENTS with the RAW option isn't supported in SQL Server 2000, but it does work with AUTO.

Edit:

If you were to run the query to get the list of servers in SQL Server 2000, you'd receive a message like this:

Msg 6825, Level 16, State 1, Line 1
ELEMENTS mode requires FOR XML AUTO.

That would come after you found that sys.servers doesn't exist in SQL Server 2000.

This is the query you'd need to run to get the data you want. I've updated the column names because SQL Server 2000 doesn't like duplicated column names:

SELECT
    srvname AS [TD1]
    ,srvproduct AS [TD2]
    ,providername AS [TD3]
    ,datasource AS [TD4]
    ,isremote AS [TD5]
FROM dbo.sysservers
ORDER BY
    isremote
    ,srvname
FOR XML AUTO, elements

That query works in 2000. From there, I think it's a problem with the nested query not working in 2000 as it's expected to in 2005+. If I have some extra time, I'll play with it some more.

Edit edit:

As I had suspected, but couldn't confirm, FOR XML doesn't work in a subquery in SQL Server 2000. This page has a post from Erland Sommarskog stating as much, and he directs the person who asked a similar question to this Books Online page for SQL Server 2000 where you can see the very first point states

  FOR XML is not valid in subselections, whether it is in UPDATE, INSERT, or DELETE statements, a nested SELECT statement, or other
  statements (SELECT INTO, assignment).

So, what you're trying to do in SQL Server 2000 can't be done.


mongo replication not happening I setup master/slave replication of mongodb on EC2. But I see not replication happening. When I do "show dbs" on master, it shows all dbs expected.

But when I do the same on replica, it does not show me any db.

Please help me troubleshoot.

rs.config()
{
    "_id" : "ittw",
    "version" : 1,
    "members" : [
        {
            "_id" : 0,
            "host" : "ip-10-304-48-93:27017"
        }
    ]
}

rs.config()
{
    "_id" : "ittw",
    "version" : 2,
    "members" : [
        {
            "_id" : 0,
            "host" : "domU-17-31-19-16-88-5F:27017"
        },
        {
            "_id" : 1,
            "host" : "ec2-50-321-52-908.compute-1.amazonaws.com:27017"
        }
    ]
}

rs.status() // replica
{
    "set" : "ittw",
    "date" : ISODate("2013-08-12T06:55:57Z"),
    "myState" : 1,
    "members" : [
        {
            "_id" : 0,
            "name" : "$ip:27017",
            "health" : 1,
            "state" : 1,
            "stateStr" : "PRIMARY",
            "uptime" : 356039,
            "optime" : Timestamp(1375934685, 1),
            "optimeDate" : ISODate("2013-08-08T04:04:45Z"),
            "self" : true
        }
    ],
    "ok" : 1
}

rs.status()  //Master
{
    "set" : "ittw",
    "date" : ISODate("2013-08-12T06:57:19Z"),
    "myState" : 1,
    "members" : [
        {
            "_id" : 0,
            "name" : "PRI_IP:27017",
            "health" : 1,
            "state" : 1,
            "stateStr" : "PRIMARY",
            "uptime" : 356543,
            "optime" : Timestamp(1376289725, 1),
            "optimeDate" : ISODate("2013-08-12T06:42:05Z"),
            "self" : true
        },
        {
            "_id" : 1,
            "name" : "REP_IP:27017",
            "health" : 1,
            "state" : 1,
            "stateStr" : "PRIMARY",
            "uptime" : 355869,
            "optime" : Timestamp(1375934685, 1),
            "optimeDate" : ISODate("2013-08-08T04:04:45Z"),
            "lastHeartbeat" : ISODate("2013-08-12T06:57:17Z"),
            "lastHeartbeatRecv" : ISODate("1970-01-01T00:00:00Z"),
            "pingMs" : 1
        }
    ],
    "ok" : 1
}

It looks like what you have done is run rs.initiate() on both the primary and the secondary server.

In this case the outcome is that you have two different replica sets called ittw rather than a single replica set with replication.  The primary knows about both nodes, but the "secondary" you setup is also configured as a primary for a replica set only containing itself.  This definitely won't work.

As per the instructions on Deploying a replica set, you should only run rs.initiate() on the primary, and then use rs.add("hostname:port") to add secondaries to the same replica set.

To fix up your dud secondary to a clean state, you should:

stop the secondary using db.shutdownServer() or appropriate command like service mongodb stop (depending on how you installed MongoDB)
remove the files in the dbpath directory for the secondary
restart the secondary
use the mongo shell to connect to the primary, and rs.add() your secondary

If all is well you should see consistent rs.status() information on the primary and new secondary.


Replace column by LEFT OUTER join to save space I have a table t1 with 100M row. For column A, 95% of the rows have value NULL0, the other records have values from 1 to 9.

To shrink my table, I'm thinking about moving column A to table t2. t2 would contain only rows where A is not NULL0. The "original" table would then be created by doing a LEFT OUTER JOIN between t1 and t2.

Questions:

Is this a common approach?
Is there a name for this pattern?
Are there any downsides except that I have to handle two tables now in my application code?

(I use MySQL, but I guess it doesn't matter so much for this question.)

Update: As it turns out, NULL was not a good choice for my example (since depending on db engine, it might not need any storage at all). I rewrote the first sentence of my question to make it more clear what I'm asking for.

This approach is basically trading storage for compute.  While you are reducing the amount of space on disk, you are doing so at the cost of the additional join; more specifically the compute power to make that join happen.  Also, the space savings is purely dependent upon the size of the datatype of the column in question as well as the key for which you'll be using to join the tables.  The more complex your key, the more compute required and the less space saved.

In the best case scenario, using your parameters (e.g. 100M record table where column A is an INT), we'll assume the Primary Key is also a simple INT, we get a new table that is about 10% the size of the data footprint if compared to keeping this as a column on the main table:

Column A consumes ~381MB (i.e. 100,000,000 * 4 Bytes)
New Table T2 consumes ~38.1MB (i.e. 5,000,000 * 4 Bytes for the data and 5,000,000 * 4 Bytes for the key on which we'll join)
Space savings of 90%

However, what if our key is a composite key that is a combination of an INT and DATETIME columns (4 byte and 8 byte sizes, respectively), we see the new table has doubled in size to about about 20% the size of the data footprint if compared to keeping this as a column on the main table:

Column A consumes ~381MB (i.e. 100,000,000 * 4 Bytes)
New Table T2 consumes ~76.3MB (i.e. 5,000,000 * 4 Bytes for the data and 5,000,000 * 12 Bytes for the key on which we'll join)
Space savings of 80%

Still not bad you say, but would you just leave it at the one column or would you extend this pattern to other fields?  Because we're talking design paradigm, I'd expect to see this extended to more tables/fields/etc.

So the pro is a savings in space, but what are the cons?

Obviously the excessive amount of joins you now have to manage within your SQL statements as well as the compute that will be required, but deletes now also require extra statements (since we now have multiple tables to deal with and don't forget the extra compute this requires as well).  Let's not forget what could happen if that 95% data distribution changed, as data tends to do over time.

If you've got excessive compute available and are limited on disk, this approach may be viable, but more often than not, compute is much pricier than storage, especially after factoring in licensing as most products are licensed based off of core count and not size.  MySQL Community Edition is free but the other editions are not, and what about the CPUs on your server (or cloud service provider)?  Often storage is a fraction of the cost of CPU, so unless you're limited on the storage side the cost/benefit ratio doesn't work well after you factor in the additional man-hours to write the code and the potential cost to handle the extra compute needed for the additional operations.

So, to answer your questions:

  Is this a common approach?

No, not in my experience.

  Is there a name for this pattern?

This is database normalization in it's most extreme.  Most RDBMS systems are designed with 3NF/BCNF in mind, so likely that level of normalization is where your system is most cost effective.

  Are there any downsides except that I have to handle two tables now in my application code?

Yes, if this design is utilized whenever possible, at minimum, the cost of the extra compute needed to handle the join and the extra man hours required to deal with the extra code logic (e.g. extra joins, additional deletes, etc.) will be a downside.

That being said, it may make perfect sense to do this for one or two columns within a database where the disk consumption is excessive.  The design of your database, the type of data it stores, and the system it's supporting all play into when this makes sense or not.  However in those cases, I'd say you should look into table or page compression first as data has a funny way of changing such that it finds ways to break even the most well of intentioned design patterns.

UPDATE: A quick edit to further explain my answer about Normalization as people seem to be passionate this isn't accurate.  At the root of things, this approach is creating a separate table with a one-to-one relationship and overloading the meaning of NULL to mean whatever our most common value is, which would be 0 in our example scenario.  When I say overloading the meaning of NULL, we are implying that the absence of a record means said record has a default value.  Again, this design approach isn't different than any other one-to-one or one-to-many relationship between two tables, but in this case we're just implying that the absence of a record means something.  Again, this isn't a revolutionary design approach, it's just a way to overload functionality which at the end of the day is just another flavor of normalizing the data.


How could I use binary log as relay log? I has a full backup of MySQL, and binary logs from the time of starting full backup.Can I restore the data in another instance use replication like only start slave sql_thread; rather than mysql < mysql-binlog.xxxx?

If the Slave is at the state of the start of the binlogs, then simply perform a CHANGE MASTER to point the Slave at the start of the binlogs.  POS can be either 0 or 4.  (In case you see either of those.)

At that point, the Slave will rapidly "catch up".


Postgres installation on MacOSX - problem with psql I'm currently trying to install Postgres (using the installation package from http://postgresapp.com), i.e. downloading, unpacking, dragging the app into the Applications folder, launching the app. So far, so good.

However, when I then try to use the psql command I receive -bash: psql: command not found. I tried to follow the solutions in similar questions, but trying to locate the binaries through which psql or locate psql and so on all didn't work.

Am I doing something wrong? Could someone please provide guidance on how to make it work? Thanks!

Postgres.app has a short documentation here:
http://postgresapp.com/documentation/
which tells among other things:

Configure your $PATH
Postgres.app includes many command line tools. If you want to use
them, you must configure the $PATH variable.
If you are using bash (default shell on OS X), add the following line
to ~/.bash_profile:
export PATH=$PATH:/Applications/Postgres.app/Contents/Versions/9.4/bin
...

Not doing that results in the error message you're getting.


Oracle 12c database cannot log in as a local user I met a problem from Oracle 12C Database, when I try to login to the user account with commandconn test/test, I got prompt of "invalid username/password, logon denied".
when I use conn test/test@PDBORCL, I got "Could not resolve the connect identifier specified"
Can anyone please help me out? I really appreciate!!

Strangely, changing my service name to UPPERCASE has helped!

Changed case of service name to UPPERCASE in tnsnames.ora for pluggable database ("ORCLPDB" in my case).
Follow below commands:

Note: service name in uppercase while connecting ex SCOTT/TIGER@ORCLPDB

Not sure why this has worked for me !!


Slow CREATE table from subquery using SELECT .. INNER JOIN This query takes ~90 seconds:

CREATE TABLE filtered
(
    SELECT DISTINCT
    f2.cityCode,
    f2.productCode,
    f2.productDesc,
    f2.inventoryDate,
    f2.inventoryLevel
    FROM
    etm
    INNER JOIN f2avail AS f2 ON
    etm.cityCode = f2.cityCode AND
    etm.tvlDate = f2.inventoryDate
);

but the subquery on its own takes ~0.07 seconds:

-- CREATE TABLE filtered
-- (
    SELECT DISTINCT
    f2.cityCode,
    f2.productCode,
    f2.productDesc,
    f2.inventoryDate,
    f2.inventoryLevel
    FROM
    etm
    INNER JOIN f2avail AS f2 ON
    etm.cityCode = f2.cityCode AND
    etm.tvlDate = f2.inventoryDate
-- );

Why is that?

Following this solution, I ran OPTIMIZE TABLE f2avail (the one with ~246,000 rows) and the create query ran about 25% faster, but still slow.

How can I speed up saving the subquery results? This is just one step in a series of filtering stages in an internal DB for reporting. Both tables are InnoDB.

Subquery explain results:

Update

The solution was to use composite indices on both tables.

-- Add the composite indices
ALTER TABLE `etm` ADD INDEX (`cityCode`, `tvlDate`);
ALTER TABLE `f2avail` ADD INDEX (`cityCode`, `inventoryDate`);

The query went from ~90s to about 6s.

Looking on Your pictures, I can guess - for run subquery You also use MySQLWorkbench. Most of software - not return to You back all rows from dataset, by default it filter up to 1000 records (some less)

So select without ORDER BY start work and immediately stop after first XXX rows.

In case of create table:

The subquery return all rows
Insert operation always longer than SELECT ... much, much longer

Possible or not increase speed of insert? it depends from Your current server settings and configuration - may yes, may be not

Also it depend from other server loading:

Select it non-locking operations, it start work immediately
INSERT FROM SELECT (other form of CREATE from SELECT) - lock all data, so if other transactions running on this time, it spend some time for lock tables

Also You can test query with combined indexes on both tables:
(cityCode,tvlDate) and (cityCode,inventoryDate)


Executing an oracle variable string as I do in SQL Server In SQL Server I can do something like:

DECLARE @VAR VARCHAR(100);

SELECT @VAR = SELECT * FROM SYS.DATABASES

EXECUTE(@VAR)

How can I execute a string that I've created inside a variable in Oracle?

This is the query:

DECLARE BUSCACOLUNA  VARCHAR(2000);
BEGIN

 BUSCACOLUNA:= 'SELECT  '' SELECT '' || LTRIM(listagg ( '' , '' || ''"'' || T1.COLUMN_NAME || ''"'' || '' AS '' ||''"''|| T2.DESCRICAO ||''"''  ) within group  ( order by t1.column_name ),'' , '') || '' FROM TABELA_ENTRADA ''
  FROM ALL_TAB_COLUMNS T1
    INNER JOIN TABBASE T2
      ON T1.COLUMN_NAME=T2.NO_COL_TABBASE
    WHERE T1.TABLE_NAME=''TABELA_ENTRADA''
    AND T2.CD_CONTEUDO_ARQUIVO= ''X''';

 END;

The result of that dynamic SQL is something like:

  SELECT C1 AS B1, C2 AS B2...FROM TABLE

And I would like to execute this SELECT by executing the VARIABLE.

I tried with a loop, with EXECUTE IMMEDIATE but the query only returns:

  anonymous block completed

So I can understand it's working. If it was an UPDATE or INSERT, it would work, But I would like the return of that.

In Oracle you use (for dynamic SQL):

BEGIN
    EXECUTE IMMEDIATE

Or

BEGIN
    DBMS_SQL.EXECUTE()

Like the examples here: https://www.guru99.com/dynamic-sql-pl-sql.html


How to unnest and GROUP BY elements of a JSON array? Given the band table, with a json column holding an array :

id | people
---+-------------
1  | ['John', 'Thomas']
2  | ['John', 'James']
3  | ['James', 'George']

How to list the number of bands each name is part of?
Desired output:

name   | count
-------+------------
John   | 2
James  | 2
Thomas | 1
George | 1

The data type of the column people is json, as is the result of json_array_elements(people). And there is no equality operator (=) for the data type json. So you also cannot run GROUP BY on it. More:

How to remove known elements from a JSON[] array in PostgreSQL?

jsonb has an equality operator, so the "workaround" in your answer is to cast to jsonb and use the equivalent jsonb_array_elements(). The cast adds cost:

jsonb_array_elements(people::jsonb)

Since Postgres 9.4 we also have json_array_elements_text(json) returning array elements as text. Related:

How to turn JSON array into Postgres array?

So:

SELECT p.name, count(*) AS c
FROM   band b, json_array_elements_text(b.people) p(name)
GROUP  BY p.name;

It seems more convenient to get names as text instead of jsonb objects (double-quoted in text representation) and your "desired output" indicates you want / need text in the result to begin with.

GROUP BY on text data is also cheaper than on jsonb, so this alternative "workaround" should be faster for two reasons. (Test with EXPLAIN (ANALYZE, TIMING OFF).)

For the record, there was nothing wrong with your original answer. The comma (,) is just as "correct" as CROSS JOIN LATERAL. Having been defined earlier in standard SQL does no make it inferior. See:

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

Neither is it more portable to other RDBMS, and since jsonb_array_elements() or json_array_elements_text() aren't portable to other RDBMS to begin with, that's also irrelevant. The short query does not get any clearer with CROSS JOIN LATERAL IMO, but the last bit is just my personal opinion.

I used the more explicit table and column alias p(name) and the table-qualified reference p.name to defend against possible duplicate names. name is such a common word, it might also pop up as column name in the underlying table band, in which case it would silently resolve to band.name. The simple form json_array_elements_text(people) name only attaches a table alias, the column name is still value, as returned from the function. But name resolves to it's single column value when used in the SELECT list. It happens to work as expected. But a true column name name (if band.name should exist) would bind first. While that won't bite in the given example, it can be a a loaded foot gun in other cases.

Don't use the generic "name" as identifier to begin with. Maybe that was just for the simple test case.

If the column people can hold anything but a plain JSON array, either query would trigger an exception. If you cannot guarantee data integrity, you might want to defend with json_typeof():

SELECT p.name, count(*) AS c
FROM   band b, json_array_elements_text(b.people) p(name)
WHERE  json_typeof(b.people) = 'array'
GROUP  BY 1; -- optional short syntax since you seem to prefer short syntax

Excludes violating rows from the query.

Related:

When can we use an identifier number instead of its name in PostgreSQL?


Function SCOPE_IDENTITY not found When I call SCOPE_IDENTITY in Sybase, I get

  [Error] Script lines: 1-6 --------------------------  Function 'SCOPE_IDENTITY' not found. If this is a SQLJ function or SQL
  function, use sp_help to check whether the object exists (sp_help may
  produce a large amount of output).  Msg: 14216, Level: 16, State: 1
  Server: DGTSYBASEDEV101, Line: 3

This is my code:

INSERT into IVR_SESSION (PHONE_NUMBER,IVR_TYPE, CALLING_COUNTRY,ADD_DATETIME,UPDATE_DATETIME)
values ("464545454", "asdd", "AUS",getdate(),getdate())
SELECT SCOPE_IDENTITY()

The SCOPE_IDENTITY function is not available in Sybase ASE as it is specific to Microsoft Sql Server. If you want to obtain the last identity value that was generated you can use the @@IDENTITY global variable like so:

insert into sales_daily (syb_identity, stor_id)
     values (102, "1349")
select @@identity

You can find the documentation for the @@IDENTITY global variable at SyBooks Online.


Make a relation of a table with three different category of id's table I have an issue
There a table that's called "Routing" this table is for routing a task for three category of object
      -

Analyser (interne machine)
      -
subcontractor (externe )
Manually (interne worker)

how to design a relation between 5 tables

   1 - task table -> Routing table -> - analyser table
                                      - subcontractor table
                                      - Interne user table

Edit:, answers to questions in comments:

Can a task have many routings? Can a routing belong to many tasks or just one?
No, a task is routed to one routing (the routing is the manner to know where the task should be done).
Is there an Analyzer, a Subcontractor and an Interne (ManualWorker) table?
Yes, there is an analyser table, and subcontractor table, and interne table.
Can an analyser be part of many routings (of the same task)?
Yes, an analyser can be a part of many routing.
Can a subcontractor take part in many routings of the same task?
No, a subcontractor (or analyser or interne) take to one routing of the task (the task is routed to one subctr, or analyser or in interne).

If I have understood what you need is:

TASK TABLE: ID, NAME, DESCR and other attributes

ROUTING TABLE: *ID, IDTASK, INFO and other attributes**

ANALYSER TABLE: ID, IDROUTING and other attributes

SUBCONTRACTOR TABLE: ID, IDROUTING and other attributes

INTERNE USER TALBE: ID, IDROUTING and other attributes

if you want to trace better the relations, you can add in ROUTING TABLE also 3 foreign key like IDANALYSER, IDSUBCONTRACTOR and IDINTERNEUSER. This is not very efficient, because you will have always 2 values null and only one with a value.

But, you can go straight from ROUTING TABLE to the right detailed table.


MySQL: what is AUTO_INCREMENT “free space”? Quote from openark-kit. The software seems unmaintained (Google Code hosted and last commit from 2013).

  oak-show-limits: show AUTO_INCREMENT “free space”.

What is this? How-to see AUTO_INCREMENT “free space” without this tool?

It's just the max id the auto_increment can reach.

You can make the same with the following SQL :

SELECT
    t.TABLE_SCHEMA,
    t.TABLE_NAME,
    `AUTO_INCREMENT` current_id,
    if(m.max_id =-1,'18446744073709551615',m.max_id) max_id,
    (m.max_id - `AUTO_INCREMENT`) free_id,
    (`AUTO_INCREMENT`/m.max_id*100) percent_used
FROM
    INFORMATION_SCHEMA.TABLES t
    INNER JOIN
    (
    SELECT
        TABLE_NAME,
        TABLE_SCHEMA,
        CASE
            WHEN COLUMN_TYPE LIKE "tinyint%unsigned"    THEN 255
            WHEN COLUMN_TYPE LIKE "tinyint%"    THEN 127
            WHEN COLUMN_TYPE LIKE "smallint%unsigned"   THEN 65535
            WHEN COLUMN_TYPE LIKE "smallint%"   THEN 32767
            WHEN COLUMN_TYPE LIKE "mediumint%unsigned"  THEN 16777215
            WHEN COLUMN_TYPE LIKE "mediumint%"  THEN 8388607
            WHEN COLUMN_TYPE LIKE "int%unsigned"    THEN 4294967295
            WHEN COLUMN_TYPE LIKE "int%"    THEN 2147483647
            WHEN COLUMN_TYPE LIKE "bigint%unsigned" THEN 18446744073709551615
            WHEN COLUMN_TYPE LIKE "bigint%" THEN 9223372036854775807
        END max_id
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE extra like "%auto_increment%"
    ) m
    on
        t.TABLE_NAME = m.TABLE_NAME
        AND t.TABLE_SCHEMA = m.TABLE_SCHEMA
-- WHERE TABLE_SCHEMA = 'DatabaseName'
-- AND   TABLE_NAME   = 'TableName';

oak-show-limits

  NAME

  oak-show-limits: show AUTO_INCREMENT "free space"

  SYNOPSIS

  Show all AUTO_INCREMENT columns, current AUTO_INCREMENT values and
  upper bounds, usage ratio, on all schemata and on all tables:

  DESCRIPTION

  oak-show-limits looks for AUTO_INCREMENT columns, and compares current
  values with the column type. It reports those columns where the
  AUTO_INCREMENT value is higher than a given threshold. This utility
  can be used for diagnosing underestimated and overestimated data type
  ranges. Underestimated data types are those in which the
  AUTO_INCRMENET current value is approaching the upper limit.
  Overestimated data types are those where the current value is
  extremely far from the upper limit. Output is in tab delimited, where
  each row specifies:


Seaching points within a radius - performance issues i'm in trouble and completely lost with a geometry search, using 2 tables, linked by uid, each one contains about 5 millions rows.
One table with address and point coordinates, Second with user status and some other infos as username.

Deal is to find all users having coordinates within a radius of N kilometers. Central point is my proper coordinates.

First question (infos. haversine is a simple mysql function, working well; coordinates is a POINT column with a SPATIAL index)

SELECT uid, city, ROUND(111.045 * haversine(X(coordinates),
       Y(coordinates),40.7484405,-73.9856),2) as userDistance
    FROM userprofile
    JOIN ( SELECT 40.7484405 AS latpoint, -73.9856 AS longpoint,
                  10 AS radius, 111.045 as units
         ) AS g ON 1=1
    WHERE
        MbrContains(GeomFromText (
        CONCAT('LINESTRING(',
              latpoint-(radius/units),' ',
              longpoint-(radius /(units* COS(RADIANS(latpoint)))),
              ',',
              latpoint+(radius/units) ,' ',
              longpoint+(radius /(units * COS(RADIANS(latpoint)))),
              ')')),  coordinates);

This works fine, less than 0.015 to return about 5000 rows, and EXPLAIN tells me that SPATIAL index is used. But I query only the first table.

When adding an ORDER BY userDistance LIMIT 50 for example, works fine too, except the use of EXPLAIN tells me : "Using temporary; Using filesort";
So First question : is the using temporary a problem and how to avoid it ?

SECOND problem is more complex and more serious. If I query both tables with an INNER JOIN and add a WHERE clause like table2.status = 1, query will scan all rows, ignore the SPATIAL index, and take about 10 minutes.

SELECT uid, city, ROUND(111.045 * haversine(X(coordinates),
       Y(coordinates),40.7484405,-73.9856),2) as userDistance
    FROM userprofile
    JOIN ( SELECT 40.7484405 AS latpoint, -73.9856 AS longpoint,
                  10 AS radius, 111.045 as units
         ) AS g ON 1=1
    INNER JOIN users on userprofile.uid = users.uid
    WHERE
        MbrContains(GeomFromText (
        CONCAT('LINESTRING(',
          latpoint-(radius/units),' ',
          longpoint-(radius /(units* COS(RADIANS(latpoint)))),
          ',',
          latpoint+(radius/units) ,' ',
          longpoint+(radius /(units * COS(RADIANS(latpoint)))),
          ')')),  coordinates) AND user_status = 1;

Putting the users table in a sub query provided the same results : 10 minutes.

I try multiple solutions, but still lost. thanks

Question 1:  The temp table and filesort are unavoidable; don't worry about it.

Question 2:  Let's remove the JOIN... and change AND status=1 into

AND EXISTS ( SELECT * FROM users
                WHERE uid = userprofile.uid
                  AND status = 1 )

(I assume uid is the PRIMARY KEY of users?)

In theory this will delay force the test for status until after the Spatial test.


how to change the publication settings via T-SQL? I set up a new server to host some database publications.

On the same server I set the distributor db.

All goes on well, then while running a snapshot of a publication on transactional replication:

get an error that the folder where the publication is set to run the snap does not have the privileges

I check where the snapshot is set to be generated within the publication
it is not where it should be

I cannot connect to the server, either to change the publication settings nor to grant the relevant permissions

question?
can I change the publication on that server from my local server and re-run the snap?

From my local ssms, I connect to the server where the publications are and run the following command to change the publication and set the snapshot folder to a different location:

the script below also marks that subscription for reinitialization and starts the snapshot agent.

use [my_database_publisher]
EXEC sp_changepublication
@publication = 'My_Publication_Pub', --Enter your publication name
@property = 'alt_snapshot_folder' ,
@value = 'G:\Backup\ReplData',
@force_invalidate_snapshot=1
GO
--Cannot make the change because a snapshot is already generated.
--Set @force_invalidate_snapshot to 1 to force the change and invalidate the existing snapshot.

--================================================================================================================================================
-- Marks the subscription for reinitialization. This stored procedure is executed at the Publisher for push subscriptions.
-- https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-reinitsubscription-transact-sql?view=sql-server-2017
-- Execute at the Publisher to reinitialize the push subscription.
--================================================================================================================================================
EXEC sp_reinitsubscription
     @publication = N'my_publication_Pub',
     @subscriber = N'my_subscriber',
     @destination_db = N'ORCA_Repl_Sub', --the destination database on the subscriber server
     @article ='one_or_all_of_the_articles' --you can specify the name of one article or put `all` for all articles
GO

--========================================================================
-- Start the Snapshot Agent job.
--========================================================================
use [ORCASTG_CA18_Repl]
EXEC sp_startpublication_snapshot
     @publication = N'my_publication_Pub',
go


How to check in a trigger if the entire row remains the same after an update? Of course i could do it for every column comparing like this:

if (old.column1 = new.column1 and old.column2 = new.column2...)

But it would b hard coded and hard to maintain if I add another column in the future, for example.

Is there a way to check if all the columns remain the same without checking every individual columns manually?

You can simply compare the old and the new records using is not distinct from which handles NULL values correctly (if all columns are defined as NOT NULL you can simply use = or <>)

if old is not distinct from new then
   .... do something
end if;

The same can be done to check if at least one column changed:

if old is distinct from new then
   .... do something
end if;


SQL Server TRY..CATCH resume on terminating error I'm trying to get a master stored procedure to execute through to completion, where the master SP calls child SPs to perform modular tasks that are independent of each other (i.e. - if one fails, that shouldn't halt execution of the others). One of these child SPs might fail, and I don't know in advance why it might fail, but one scenario I have encountered is that it's trying to select from a table that does not exist. I would like that error logged, and then for the master script to continue. I'm guessing that selecting from a non-existent table is a terminal error, and I want to know if there is a way to continue even on terminal errors.

(Yes, I know that I can interrogate the information schema to determine table existence, but I'm looking to catch errors that haven't been defensively coded against yet - today it could be a missing table, tomorrow it could be something else we haven't considered that brings the master script to a screaming halt.)

Example code:

DROP TABLE IF EXISTS my_error_log;
CREATE TABLE my_error_log (
    error_time DATETIME,
    error_msg VARCHAR(MAX)
);
GO

CREATE OR ALTER PROCEDURE failProc
AS
BEGIN
    BEGIN TRY
        -- This one fails, is caught, and continues...
        -- SELECT 1/0;
        -- This one fails, is caught, and terminates the master script...
        SELECT * FROM non_existent_table;
    END TRY
    BEGIN CATCH
        INSERT INTO my_error_log
        SELECT CURRENT_TIMESTAMP, ERROR_MESSAGE();
    END CATCH
END;
GO

CREATE OR ALTER PROCEDURE masterFailProc
AS
BEGIN
    SET XACT_ABORT OFF;
    BEGIN TRY
        INSERT INTO my_error_log SELECT CURRENT_TIMESTAMP, 'start';
        EXEC failProc;
        EXEC failProc;
        EXEC failProc;
        INSERT INTO my_error_log SELECT CURRENT_TIMESTAMP, 'end';
    END TRY
    BEGIN CATCH
        INSERT INTO my_error_log SELECT CURRENT_TIMESTAMP, 'fatal error';
    END CATCH
END;
GO

EXEC masterFailProc
GO

SELECT * FROM my_error_log;
GO

DROP PROCEDURE masterFailProc;
DROP PROCEDURE failProc;
DROP TABLE my_error_log;

I've found a solution, not as elegant as I'd hoped, but it works. If someone has something better I'd like to hear it.

The problem with the masterFailProc is it's wrapping a TRY..CATCH block around the entire script, instead of around individual EXEC calls. If the definition is changed as below, then it will happily log errors on a per-exec basis rather than terminating the entire script:

CREATE OR ALTER PROCEDURE masterFailProc
AS
BEGIN
    SET XACT_ABORT OFF;
    BEGIN TRY
        INSERT INTO my_error_log SELECT CURRENT_TIMESTAMP, 'start';
        BEGIN TRY EXEC failProc; END TRY BEGIN CATCH /* log error */ END CATCH;
        BEGIN TRY EXEC failProc; END TRY BEGIN CATCH /* log error */ END CATCH;
        BEGIN TRY EXEC failProc; END TRY BEGIN CATCH /* log error */ END CATCH;
        INSERT INTO my_error_log SELECT CURRENT_TIMESTAMP, 'end';
    END TRY
    BEGIN CATCH
        INSERT INTO my_error_log SELECT CURRENT_TIMESTAMP, 'fatal error';
    END CATCH
END;
GO


Can't restore database: A transport-level error has occurred when receiving results from the server This is not a good day for me. I have a backup (master.bak) for SQL Server 2005 Express SP4 (v.9.0.500) that I want to restore to my master db instance. I am running SQL Server in single-user mode (sqlservr.exe -m), connect to the instance, then I do a Task... Restore. This is the error I get:

  TITLE: Microsoft SQL Server Management Studio Express

  Restore failed for Server 'GINO\SQLEXPRESS'.
  (Microsoft.SqlServer.Express.Smo)

  For help, click:
  http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.2047.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Restore+Server&LinkId=20476

  ------------------------------ ADDITIONAL INFORMATION:

  Failed to retrieve data for this request.
  (Microsoft.SqlServer.Express.SmoEnum)

  For help, click:
  http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476

  An exception occurred while executing a Transact-SQL statement or
  batch. (Microsoft.SqlServer.Express.ConnectionInfo)

  A transport-level error has occurred when receiving results from the
  server. (provider: Shared Memory Provider, error: 0 - The pipe has
  been ended.) (Microsoft SQL Server, Error: 109)

  For help, click:
  http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=109&LinkId=20476

Please help me restore my backup database so that I can continue along with my business!

Use SQLCMD instead of Management studio to do the restore and try this.  (Since you are using a named instance make sure to use the -S switch for SQLCMD)

C:\> SQLCMD -S GINO\SQLEXPRESS
> RESTORE DATABASE master FROM DISK='C:\SQLBackups\master.bak' WITH REPLACE;
> GO

Obviously replacing the path to your backup with the real path of where your backup is.

Have a look here for (slightly) more information.


Read transaction log records I am reading Remus Rusanu article on tlog.In that article he demoed on how to read log payload by using dbcc page option .My question is ,is there a way to read logrecord output of fn_dblog(null,null) from sql or c#.PFB screen shot for more details.

My question is ,is there a way to read logrecord output of fn_dblog(null,null) from sql

Reading transaction log using fn_dblog is not supported by Microsoft. So you should avoid running this command on production server. You can run this on UAT/Test environment.

What do you exactly want to read from output of fn_dblog, can you be precise. There are myraids of information you can get from output of fn_dblog. Its not easy to read the output and one requires some level of expertise to read it.

I will give you a Demo.

BEGIN TRANSACTION TRANSACTION_NAME
SQL QUERY
COMMIT TRANSACTION OR ROLLBACK TRANSACTION

--Then use below query to filter out records related to the transaction

USE DATABASE_NAME
GO
SELECT
OPERATION,
Context,
[Transaction Name],
[AllocUnitId],
[Page ID],
Description,
[Lock Information]
FROM FN_DBLOG(NULL,NULL)
WHERE [Transaction ID] in
(
select [Transaction ID] from
fn_dblog(Null,Null)
where [Transaction Name]='TRANSACTION_NAME'
)

You can run

SELECT * FROM FN_DBLOG(NULL,NULL)

To see all column names which fn_dblog would produce

Example: I would show what all things are logged when simple update command is fired

Begin tran T1

update [dbo].[IXBUILD] set C2='XXXXXX' where C1=1

Commit

Its has one row for begin tran one for commit and one regarding update operation. You can see LOP_BEGIN_XACT for beginning of transaction and COMMIT for commit of transaction.
LOP_MODIFY_ROW says a row was modified like we did in update statement.
LCK_Clustered index is in picture because table had CI and row must have been exclusively locked for update
Then comes hexadecimal page ID which actually says what page had row which was modified
Then last column Includes the lock Information. The lock which was taken on

HoBt 72057594041860096:ACQUIRE_LOCK_IX OBJECT: 8:613577224:0 ;ACQUIRE_LOCK_IX PAGE: 8:1:344 ;ACQUIRE_LOCK_X KEY: 8:72057594041860096 (8194443284a0)

Database ID=8 file number 1 and page 334. You would also see KEY value
There is also a log sequence number for every transaction and column Log Record Length which would tell you LSN and size of log record for particular operation

This is just a bit of information. There is too much to derive from content of transaction log and it would be difficult to write down all here. But I can suggest you various blogs of Paul Randal where you can find him writing about how to take help of the output of fn_dblog to get various information.

Time Pass with Transaction Log

Another dive into Transaction Log

Read various articles by Paul you would learn a lot about contents of transaction log

Tracking page split using Transaction Log

How to tell who changed Log file characteristics

Using fn_dblog,fn_dump_dblog and restoring STOPBEFOREMARK using LSN


The future of DBAs with ORM I am an aspiring DBA (I'd say in ~3 years I would be ready to apply for a job) and I currently am a programmer. I relayed my future goals to a friend of mine who is part of an Agile SCRUM team in a very heavy and fast-paced software development company. (SDLC to the core).

He expressed his concerns for my career path goals - stating that DBAs for most software dev companies is a dying position due to the increase and development of ORM in the last ~ 5 - 7 years. He said that the company trusts the programmers and most of them are capable of doing most of the work DBAs do. (Stored procedures, encryption, SQL, backups, ddl, profiling, etc.)

Can anyone shed light on their experiences or if they have the same concerns/beliefs with the DBA position?

I am referring to NHibernate and those kinds of systems.

To stay aligned with the Q&A formatting of this site - What are problems that will be faced in the future if everything is converted to and uses ORM and DBA positions are no longer weighing in with the same importance they have been the past decade or two?

I will be honest and admit, aside from basic research I have no first hand experience with ORM. I know this site is supposed to be used for professionals, which is why I feel those who know exponentially more about this circumstance would be able to better identify concerns/problems.

That is how it tends to work in small companies, but that is how it always used to work for small companies: at a small scale an expert database person was often seen as a grandiose expense when all the devs can do the basics. This is not really a factor of ORMs and noSQL becoming popular recently.

ORMs still have datastores behind them that someone needs to understand and be able to monitor, backup, repair, analyse, scale & optimise and as companies and/or projects grow it still pays to have a specialist in that area. Trusting devs with no data handling specialism is often fine on a small scale, but can lead to significant problems when scaling up. It is quite common to see people gush about an ORM or noSQL solution and claim it means they don't need to worry about the database, only to complain bitterly later when something they tested on a few thousand records falls to the floor when asked to deal with tens of thousands or millions or more (or sometimes even "a few thousand plus one" for really bad designs) and they have quickly got to the point where throwing more hardware at the problem is no use. You'll often find massively complicated caching layers getting created which would not be required if a little more thought had gone into either the initial design of the data store (or a later refactor) - those layers need development and maintenance too so those projects are often not saving money long term by not having a "data guy". In fact if you can keep a foot in both camps (dev and data) then there is much interesting work (and good money) in certain "architect" roles: people either designing the model for projects or earning their bread from taking overly complicated & inefficient monstrosities and helping the dev teams turn them into relatively sleek & scalable creations.

Also don't constrain yourself to being a DBA for a dev team: all of our clients (banking types, councils, a small airline or two) have an infrastructure department in part populated by DBAs who are responsible for making sure their data is appropriately handled. They challenge us (and other suppliers) to ensure we are following good practise, they are responsible for wider issues such as integration of data from systems from desperate suppliers, and they are of course responsible for maintaining in-house databases (mirrors, backups, monitoring, ...) - so there are other places a DBA optimised skill-set is required.

Of course you must stay up-to-date with the newer tools, that goes without saying, but while the job overall has evolved a bit over the last decade it hasn't changed nearly as much as some would have you believe (nor I expect will it in the near future, though keep your ear to the ground for new developments to stay current and avoid obsolescence if I'm wrong about that).


How can I optimize dashboard performance? I have a table like
FieldA, FieldB, FieldC, FieldD, TheDate, Count

I have a web app which provides a dashboard of the top few Count by each of the fields.  Not wanting to prematurely optimize, the original, brute force queries for these charts are like:
SELECT TOP 10
    FieldA,
    SUM(Counts) AS Counts
FROM TheTable
WHERE @StartDate <= TheDate AND TheDate <= @EndDate
GROUP BY FieldA
ORDER BY Counts

And the same for the other fields.  But the server ends up selecting by date range independently for each chart and when there is a lot of data, the system is bogging down.
It seems wrong to get all the data in the app (once) then do the summary by the columns locally.  And maybe the RDBMS caches a lot of the records so the second through fourth chart are more efficient that the first.
I'm using Azure SQL and neither SQL Server Management Studio nor DataGrip suggest any missing indices which could help.
Any thoughts on techniques to do multiple, similar options on the same data?  In general or for Azure SQL.  Thanks.

suggest any missing indices which could help

You need to show what indexes you currently, and the size & shape of your data (approx how many rows historically?, and how many new are added each day/hour/other?, how much do fields vary?, how often are they NULL?), have defined for us to give absolutely relevant advice.

Any thoughts on

As most of your queries follow the stated pattern, you are always performing a query on a range of dates so at very least you need an index on [date]. Further more it would probably be a good idea for this to be your clustered index[†] to reduce the page accesses needed for such range queries.
If you can afford the extra space (and the extra RAM needed for your common working set to stay in there to avoid IO thrashing) then you may get a noticeable boost from having indexes on [date],FieldA, [date],FieldB, ... so that the grouping does not have to perform a sort operation (once the data is found by date it is already in order in the index used). If there are particular fields that are queried for much more often than the others, then perhaps just do this to help the queries on those fields instead of spending the resources doing it for all of them.
[†] even if you have a unique integer as your primary key[‡] (or something else like a UUID)
[‡] and you should have a surrogate key in this example otherwise you could have rows that are otherwise identical which doesn't fit the relational model and could cause issues
As a side-note: date is a keyword, as it is a type, so I would avoid using that as a column name even in examples.


What's the cause for an AlwaysOn AG Secondary Replica to have a high Redo Queue Size and Estimated Recovery Time with a good Redo Rate? SQL14 is the Primary server, SQL16 is the Secondary Replica, and they're setup using synchronous commit availability mode:

As of yesterday it appeared that data stopped synchronizing from the primary to the replica. This morning we Suspended the Availability Database and then Resumed it. That seemed to kick the synchronization again as I see new data coming through now but the Redo Queue Size and Estimated Recovery Time in the dashboard are still big and are continuing to grow.

What things can I check / do to fix this?

Additional Information:
Server Version: SQL Server 2016 Enterprise - SP1 (on both primary and secondary)

Also, we had some long running index re-organize / re-build jobs fail on the primary earlier in the morning. (That was about 4 hours ago but could that still be potentially a contributor to this issue now?)

I noticed the following server logs on the secondary (from oldest to newest):

Not sure if there's any clues in there?

DBCC OPENTRAN on the Secondary returns the following:

Extended events session to track waits on the Secondary:

So a number of things here... (e.g. this will be a long answer and may go through a number of iterations):

First and foremost did this AG experience a failover?  If not, then checking the ERRORLOGs on the Primary node is where I suggest you start your troubleshooting process; if a failover did occur, check the ERRORLOGs on the node formally known as the Primary to see what happened.  You can use the clunky UI within SSMS or you can use the undocumented extended procedure xp_readerrorlog, which is my recommendation as it's just quicker to filter out noise this way.  For instance, I'd suggest starting out looking for any references to the AG name like the following and digging from there:

xp_readerrorlog 0, 1, N'>>YOUR AG NAME HERE<<', NULL, '2019-11-18 12:00:00.000', '2019-11-19 15:00:00.000', NULL, NULL

Check the link above for syntax surrounding the command.

Based on what you find, expand the search checking your Windows Event Logs (eventvwr.msc), Cluster Logs (cluadmin.msc), Application logs, etc. using error times as a point of reference if necessary.  I highly suspect these logs will identify what caused the synchronization to stop, be it a cluster-related issue, a hiccup due to maintenance, etc.  Based on that, I suggest posting a new question if you are having a hard time interpreting the results.

Second, running Index Maintenance in an AG will cause AG sync delays.  There's no getting around it, regardless the version of SQL Server (SQL 2019 included) though the new versions tend to recover quicker.  If you feel you must run Index Maintenance and you have the ability to take the application offline, I suggest your run offline Index Maintenance.  Execute this as offline REBUILDs with MAXDOP defined, as offline index rebuild operations don't cause as severe of delays within your AG.  Obviously this approach will cause an outage, so it's not something to do lightly.  I do support an environment where we do this as neglecting index maintenance causes unnecessary growth (and in a multi TB system that causes other problems)

To put it plainly, a common misconception in the SQL Server community is that Index Maintenance is super critical to the performance of a database.  This generally just isn't the case as Index fragmentation has only minimal impact on execution plan behavior. Only when average white space per page approaches egregious levels, does fragmentation start to matter. Honestly, in most cases Index maintenance is basically a very expensive stats update operation, meaning be on top of Stats Updates which don't generally cause blocking and also don't back up AGs.

Finally, if you want to see how far behind secondaries are and get an estimate of how long it'll take them to catch up, give this query a try which is a variant of this code from Jonathan Kehayias:

SELECT ar.replica_server_name,
       adc.database_name,
       ag.name AS ag_name,
       drs.is_local,
       drs.synchronization_state_desc,
       drs.synchronization_health_desc,
       drs.last_redone_time,
       drs.redo_queue_size,
       drs.redo_rate,
       (drs.redo_queue_size / drs.redo_rate) / 60.0 AS est_redo_completion_time_min,
       drs.last_commit_lsn,
       drs.last_commit_time
FROM sys.dm_hadr_database_replica_states AS drs
INNER JOIN sys.availability_databases_cluster AS adc
       ON drs.group_id = adc.group_id AND
       drs.group_database_id = adc.group_database_id
INNER JOIN sys.availability_groups AS ag
       ON ag.group_id = drs.group_id
INNER JOIN sys.availability_replicas AS ar
       ON drs.group_id = ar.group_id AND
       drs.replica_id = ar.replica_id
ORDER BY
       ag.name,
       ar.replica_server_name,
       adc.database_name;


Query to seed many to many table How can I write a query to seed a many to many table. Let's say I have an Employee table, a Branch table, and I am introducing an EmployeeBranch table for the many to many relationship.

I need to insert records for every employee and branch combination into the EmployeeBranch table. So if there are 100 employees and 10 branches, I would end up with 1000 records; each employee will have 10 records for each branch.

If you're using SQL Server, You can use CROSS APPLY to return 1 Branch row for each Employee row:

SELECT e.ID AS EmployeeID,
  e.Name AS EmployeeName,
  b.BranchID,
  b.BranchName
FROM Employee e
CROSS APPLY (SELECT ID AS BranchID, BranchName FROM Branch) b
ORDER BY EmployeeID

See working db<>fiddle.

For Postgres, similar query but using LEFT JOIN LATERAL instead:

SELECT e.ID AS EmployeeID,
  e.Name AS EmployeeName,
  b.ID AS BranchID,
  b.BranchName
FROM Employee e
LEFT JOIN LATERAL (SELECT ID, BranchName FROM Branch) b ON true
ORDER BY EmployeeID

Working db<>fiddle here.


2 Date Ranges - How many days from one range excluding weekends overlap First - Thanks for your help.

I'm looking to create a query in SQL that provides the me the # of days from one date range that overlap another date range and excludes weekends.

Example Data:

Training Dates - 10/16/2014 - 10/28/2014
Fiscal Month Dates - 09/22/2014 -10/21/2014

Number of Days the training overlaps the Fiscal Month Excluding Weekends: 4 Should be the Answer (16th, 17th, 20th, and 21st)

Again Thanks for your help!!

Create date tables or table variables with your ranges and do the join query. Assuming SQL Server as the database, my sample solution is given below.

Declare @Range1Begin date = '10/16/2014',
        @Range1End   date = '10/28/2014',
        @Range2Begin date = '9/22/2014',
        @Range2End   date = '10/21/2014';

Declare @DateTable1 table (Date1 date);

Insert into @DateTable1
select dateadd(day, number, @Range1Begin)
from
    (select distinct number from master.dbo.spt_values
     where name is null) n
where dateadd(day, number, @Range1Begin) <= @Range1End;

Declare @DateTable2 table (Date2 date);

Insert into @DateTable2
select dateadd(day, number, @Range2Begin)
from
    (select distinct number from master.dbo.spt_values
     where name is null ) n
where dateadd(day, number, @Range2Begin) <= @Range2End;

-- Get individual dates
select dt1.Date1 as date, datename(w, dt1.date1) as weekday from @DateTable1 dt1
inner join @DateTable2 dt2
on dt1.date1 = dt2.date2
where datepart(w, dt1.date1) not in (1,7);

-- Get total days
select count(*) as TotalDays from @DateTable1 dt1
inner join @DateTable2 dt2
on dt1.date1 = dt2.date2
where datepart(w, dt1.date1) not in (1,7);

Without declaring table variables, following is the solution.

Declare @Range1Begin date = '10/16/2014',
        @Range1End   date = '10/28/2014',
        @Range2Begin date = '9/22/2014',
        @Range2End   date = '10/21/2014';

-- Get total days
select count(*) as TotalDays from (select dateadd(day, number, @Range1Begin) date1
from
    (select distinct number from master.dbo.spt_values
     where name is null) n
where dateadd(day, number, @Range1Begin) <= @Range1End) dt1
inner join (select dateadd(day, number, @Range2Begin) date2
from
    (select distinct number from master.dbo.spt_values
     where name is null ) n
where dateadd(day, number, @Range2Begin) <= @Range2End) dt2
on dt1.date1 = dt2.date2
where datepart(w, dt1.date1) not in (1,7);


Activity Monitor Blocked by Firewall. Set Static Port? We are seeing an issue with not being able to open Activity monitor for remote SQL servers.

The Activity Monitor is unable to execute queries against server .
Activity Monitor for this instance will be placed into a paused state.
Use the context menu in the overview pane to resume the Activity Monitor.

The RPC server is unavailable. (Exception from HRESULT: 0x800706BA) (mscorlib)

It appears some firewall is blocking it,
When we look at firewall logs (on the network), we are seeing blocks from 496xx. And its different port for each server.

Is anyone aware if we can make this a static port?

Activity Monitor requires the firewall to allow access to the SQL Server port (default 1433) for SQL query access and the RPC ports for WMI access for things like the Processor, Network, Disk IO and Memory graphs. This blog describes this and the required permissions for viewing all of Activity Monitor.

This article describes how to configure a static port range for RPC on a machine to allow you to open those ports on the firewall, otherwise, you have to open a substantial range of ports. Due to the number of applications that can rely on RPC ports, you shouldn't restrict this range to a single port, but instead, use a range size suitable for the number of applications and that your security team is comfortable with opening on the firewall.


How to I interleave logs from two different Oracle tables? I have two log tables, which are populated from two different PL/SQL packages. It's often useful to keep the data separate, but sometimes I'd like to "interleave" the two tables so I can see what's going on in both pieces of code. Both tables have a time-stamp column (although the columns have different names). Here's a stripped-out version of the two tables, and what I'd like as a result:

Table LOG_1:

TIME_CREATED                  ENTRY_TEXT
-------------------------------------------
11-SEP-12 12.39.16.037824000  Log_1 entry 1
11-SEP-12 12.39.16.037824002  Log_1 entry 2
11-SEP-12 12.39.16.037824004  Log_1 entry 3

Table LOG_2:

TIME_CREATED                  ENTRY_TEXT
-------------------------------------------
11-SEP-12 12.39.16.037824001  Log_2 entry 1
11-SEP-12 12.39.16.037824003  Log_2 entry 2
11-SEP-12 12.39.16.037824005  Log_2 entry 3

New table LOG_COMBINED:

TIME                          ENTRY_TEXT
-------------------------------------------
11-SEP-12 12.39.16.037824000  Log_1 entry 1
11-SEP-12 12.39.16.037824001  Log_2 entry 1
11-SEP-12 12.39.16.037824002  Log_1 entry 2
11-SEP-12 12.39.16.037824003  Log_2 entry 2
11-SEP-12 12.39.16.037824004  Log_1 entry 3
11-SEP-12 12.39.16.037824005  Log_2 entry 3

(Note the [artificial] difference in the timestamps!)

I'm sure this can be done in a simple SQL statement - perhaps involving a union all?

select 'LOG_1' AS LOG_SOURCE, TIME_CREATED,ENTRY_TEXT
  FROM LOG_1

  UNION ALL

  select 'LOG_2', TIME_CREATED,ENTRY_TEXT
  FROM LOG_2

  ORDER BY 2

I formalize this kind of logic as a view so you can document and comment it.

  CREATE OR REPLACE FORCE VIEW COMBINED_LOGS

  (LOG_SOURCE,

  TIME_CREATED,

  ENTRY_TEXT ) AS

  select 'LOG_1' , TIME_CREATED,ENTRY_TEXT
  FROM LOG_1

  UNION ALL

  select 'LOG_2', TIME_CREATED,ENTRY_TEXT
  FROM LOG_2;

  COMMENT ON TABLE COMBINED_LOGS IS 'Explanatory comment goes here';

This works for me on Oracle 11g R2 and Oracle 9i, perhaps it was the use of keyword SOURCE as a column name which I have corrected to LOG_SOURCE

Edit: Corrected to UNION all after the comment. See here for more discussion on the details of UNIONS.  This should be done for performance reasons and to ensure identical records are not removed.


(Got an error reading communication packets) but no error on client i've search a lot about this thread without results.
we don't know how to resolve this:
DB : mariaDB
SERVER: Microsoft Azure
enviroment: Nodejs with Knex (pool connections, min 0 max 300)

our client app stop to catch db response, without an error, except for (Got an error reading communication packets)

if we try to connect to mysql through Navicat/TablePlus/MysqlWorkbench (or even our app in local) during the error we still receive the data from db.
we don't know how to resolve it, since wednesday.

any idea about it?

Try to increase your connection pool min size 5 instead of 0.

From DB side, verify the timeout variables  and connection pool variables like

wait_timeout & interactive_timeout >=  app connection pool timeouts.  (db timeouts should not be less than app timeouts).    And check to make sure the value of max_allowed_packet is high enough. Lastly increase enough net_read and net_write timeouts from DB side.


Adding a field to a table (but in another database) I understand that this is a strange question but I'll try to explain it the best I can.

We have a database (I'll call it MachineDB) on our MSSQL Server 2016 that gets many datasets each day from a machine (we're talking ~50k datasets/day and this database has been running for 2 years now). A dataset consists of some fields specific to that machine. Whenever there is a an update from the machine manufacturer this will be a DacPack. I understand that this is basically a schema update which will delete some tables/fields and add others. I understand there are ways to compare those schemas manually and update them manually.

So, what I would like to do is to add one field of type int to one of the tables (the table has a primary key defined). We would like to use this field to track inventory usage. It would be easy to simply add this field and fill it with the required data.

BUT, whenever an update is rolled out, this field would get deleted because the update process does some kind of schema compare and deletes fields that are not used anymore.

So the actual question is: Is there a method I can add that field into another database which references the primary database or what would be the correct approach to this?

You might be able to get away with just adding a new schema to the existing database, but either way the solution is approximately the same.

In order to avoid interfering with the dacpac deployment, you may want to avoid actually implementing a true foreign key constraint.  You can either make a stored procedure do the inserts/updates and have it manually do the constraint checking before inserting a new row.

But basically, create a table with a non-incrementing primary key (that should have a 1:1 with the source table.  Then add your custom fields to that table.  You can use a view to merge them together to make it easier to digest the contents if you want.

CREATE TABLE dbo.Extension
    (
    DatasetID INT NOT NULL PRIMARY KEY --make sure to match type to source
    , InventoryCount INT NULL
    )

As I said, if you can add it as a custom schema (assuming that survives the dacpac update process the vendor is using) then it can live inside the same database (my preference).  But you can add it into a new database if you want as well.


What are the options of reducing tempdb latency? Took the job of monitoring and tununing a SQL Servers 2012 Enterprise Ed. SP1 (64 bit) on Windows Server 2008 R2 Standard Ed.

All of them are running inside virtual machines. One SQL Server per a regional office on a  single RAID10 or RAID5. In other words, I do not have possibility to configure an SQL Server using different physical units (hard drives).

I observe an elevated reading and writing latency on tempdb system database.
What are my options in reducing this latency of tempdb database?

You've got two basic options.

Reduce the workload required by the tempdb database
Get faster hard drives

As for #1 look for indexes in other databases which are missing.  Fixing missing indexes will cause less spill to the tempdb database which will reduce the workload.

If the applications on the server are using temp tables a lot there's nothing you can do but get faster hard drives.

Now all that said, if the latency problem is on the PFS pages in the tempdb database (you'll need to find the specific page IDs which are having latency issues) then the fix is easy, add more tempdb database files.


Access 2007 sql editor Is there an ACCESS 2007 JET SQL editor or client (not sure the right term is) that can be used to code Access Jet SQL queries? It is very difficult to write SQL queries in ACCESS 2007 since it's like basically typing in notepad without any special text formatting for keywords or commands for example. Its hard to write and read... Any suggestions or tools that I can use to make this process easier?

There are many tools and ways better ways to do SQL editing  for Access 2007 other then using msword , at least i would use Notepad++

below are tools i used and are good

RazorSQL
SQuirreL SQL

here is a 2 post1 and post2 in Stackoverflow similar to your issue

but if you want my opinion to this problem, the best thing i suggest is to use SSMS and add a linked server to your Access file, i like SSMS


VarBinary(max) updates very slow on SQL Azure We store some documents in a SQL Server database VarBinary(Max) column. Most documents will be a few KB, but sometimes it maybe a couple of MB.

We run into an issue when the file becomes bigger than about 4MB.

When updating the VarBinary column in a on-prem SQL Server, it is very fast (0.6 seconds for a 8MB file).

When doing the same statement on a identical database on SQL Azure, it takes more than 15 seconds!

Also if the code is running from an Azure App Service it is very slow. So it's not our Internet connection that is the problem.

I know storing files in SQL Server is not the preferred way of storing and Blob storage would normally the best solution, but we have special reasons we need to do this, so I want to leave that out of the discussion ;-)

When investigating the execution plans, I see a "Table Spool" taking all the time and I'm not sure why. Below are the execution plans for on prem and Azure.

Identical databases and data. If someone can help, that would be great.

Thanks Chris

On Prem SQL 2016

Azure SQL database

The on-prem instance was just a local install on my surface. The other is a 50eDTU elastic pool on Basic-tier. After the comment I changed it to a standard tier and the query now takes about 2 seconds instead of more than 15!
So I think that's the answer!

I never thought that a local install of SQL on a surface together with all other software like Visual Studio etc running, would out perform SQL Azure by 25 times! (Even on a basic tier). But now I know :-)


stats_column_id and index_column_id do not update with physical order of clustered index is changed Unless I'm misunderstanding the purpose of the column, the following code indicates that a change of the structure of the clustered index does not change the ordinal position (stats_column_id)of the column in the sys.stats_columns DMV. (Tested in AdventureWorks2014, AdventureWorks2008R2)

select i.name, c.name, ic.column_id, ic.index_column_id
from sys.indexes i
join sys.index_columns ic
    on i.object_id = ic.object_id
    and i.index_id = ic.index_id
join sys.columns c
    on i.object_id = c.object_id
    and ic.column_id = c.column_id
where i.name = 'PK_BusinessEntityAddress_BusinessEntityID_AddressID_AddressTypeID'
order by ic.key_ordinal;

select sh.name,s.name, c.name, c.column_id, sc.column_id, sc.stats_column_id
from sys.stats s
join sys.stats_columns sc
    on s.object_id = sc.object_id
    and s.stats_id = sc.stats_id
join sys.columns c
    on s.object_id = c.object_id
    and sc.column_id = c.column_id
join sys.tables t
    on s.object_id = t.object_id
join sys.schemas sh
    on t.schema_id = sh.schema_id
where s.name = 'PK_BusinessEntityAddress_BusinessEntityID_AddressID_AddressTypeID'
order by sc.stats_column_id;

dbcc show_statistics('[Person].[BusinessEntityAddress]','PK_BusinessEntityAddress_BusinessEntityID_AddressID_AddressTypeID') with density_vector;

ALTER TABLE [Person].[BusinessEntityAddress] DROP CONSTRAINT [PK_BusinessEntityAddress_BusinessEntityID_AddressID_AddressTypeID]
GO

ALTER TABLE [Person].[BusinessEntityAddress] ADD  CONSTRAINT [PK_BusinessEntityAddress_BusinessEntityID_AddressID_AddressTypeID] PRIMARY KEY CLUSTERED
(
    AddressID ASC,
    [BusinessEntityID] ASC,
    [AddressTypeID] ASC
)
GO

select i.name, c.name, ic.column_id, ic.index_column_id
from sys.indexes i
join sys.index_columns ic
    on i.object_id = ic.object_id
    and i.index_id = ic.index_id
join sys.columns c
    on i.object_id = c.object_id
    and ic.column_id = c.column_id
where i.name = 'PK_BusinessEntityAddress_BusinessEntityID_AddressID_AddressTypeID'
order by ic.key_ordinal;

select sh.name,s.name, c.name, c.column_id, sc.column_id, sc.stats_column_id
from sys.stats s
join sys.stats_columns sc
    on s.object_id = sc.object_id
    and s.stats_id = sc.stats_id
join sys.columns c
    on s.object_id = c.object_id
    and sc.column_id = c.column_id
join sys.tables t
    on s.object_id = t.object_id
join sys.schemas sh
    on t.schema_id = sh.schema_id
where s.name = 'PK_BusinessEntityAddress_BusinessEntityID_AddressID_AddressTypeID'
order by sc.stats_column_id;

dbcc show_statistics('[Person].[BusinessEntityAddress]','PK_BusinessEntityAddress_BusinessEntityID_AddressID_AddressTypeID') with density_vector;

However, the density vectors indicate a change in the leading column of the index/statistics object. Is this a fundamental misunderstanding on my part? If so, how would I find the leading column of a statistics object using DMVs?

Tested SQL Server versions: 2008R2, 2014

I was having the same issue while trying to reproduce the way others retrieve index information from the sys.dm views in SQL Server. I just couldn't figure out the order of the columns in the index.

Following is a script I created to determine the order of the columns in any given index for a given table:

SELECT s.name                  AS Schema_name,
       o.name                  AS Table_Name,
       i.type_desc             AS Index_Type,
       i.name                  AS Index_Name,
       c.name           AS Table_Column,
       i.fill_factor           AS Indx_Fill_Factor,
       ic.key_ordinal          AS [Key_ordinal (IDX Column_Order)],
       ic.index_column_id      AS Index_column_id,
       stc.stats_column_id     AS Stats_Col_ID,
       -- Additional info for each joined table
       -- comment out what you don't need
       -- 2 lines at a time
       --
       -- '| table object -->', -- column seperator
       -- o.*,
       -- '| schema object-->', -- column seperator
       -- s.*,
       '| index info-->', -- column seperator
       i.*,
       '| sys index info -->', -- column seperator
       si.*,
       '| indx cols info -->', -- column seperator
       ic.*,
       '| tab cols info -->', -- column seperator
       c.*,
       '| idx stats info -->', -- column seperator
       st.*,
       '| idx stats columns info -->', -- column seperator
       stc.*
FROM   sys.objects             AS o
       JOIN sys.schemas        AS s
            ON  s.schema_id = o.schema_id
       JOIN sys.indexes        AS i
            ON  i.object_id = o.object_id
       JOIN sys.sysindexes as si
            ON  si.[id] = i.object_id
            AND si.indid = i.index_id
       JOIN sys.index_columns  AS ic
            ON  ic.object_id = i.object_id
            AND ic.index_id = i.index_id
       JOIN sys.columns        AS c
            ON  c.object_id = ic.object_id
            AND c.column_id = ic.column_id
       JOIN sys.stats          AS st
            ON  st.object_id = i.object_id
            and st.stats_id = i.index_id
      JOIN sys.stats_columns  AS stc
      ON c.column_id = stc.column_id
      AND stc.stats_id = st.stats_id
      AND stc.[object_id] = o.[object_id]
WHERE  1=1
     --and i.type <> 1 -- Exclude Clustered Indexes. 0 = Heap; 1 = Clustered Index, 2 = Non-Clustered Index
       AND s.name != 'sys' -- Exclude sys items
       and o.name = 'BusinessEntityAddress'
       AND i.name = 'PK_BusinessEntityAddress_BusinessEntityID_AddressID_AddressTypeID'
ORDER BY
       o.object_id,
       i.index_id,
       ic.key_ordinal

The column key_ordinal in the sys.index_columns table is the order in which the columns are stored in the index.

There isn't a key_ordinal column for the sys.stats_columns table. The column stats_column_id just replicates the index_column_id column of the object it references.

There is a slight difference in the wording of the article sys.stats_columns (Transact-SQL) for the column stats_column_id:

  1-based ordinal within set of stats columns.

...and in the article sys.index_columns (Transact-SQL) for the key_ordinal column:

  Ordinal (1-based) within set of key-columns.

I reckon that the index_column_id (sys.index_columns) and stats_column_id (sys.stats_columns) are the equivalent of each other and that only the sys.index_columns table has an ordering column, namely key_ordinal.


changed password for postgres login in pgAdmin, now can't connect to server I was trying to use pg_dump to script out a database and in the process I used pgAdmin to change the password for the postgres user. I realize (now) that changing that password was a bad idea. But now I can't get back in to the server to undo it. Postgres is running:

$ ps aux | grep postgr
postgres 18888  0.0  0.1 206480 13984 ?        S    16:39   0:00 /opt/PostgreSQL/9.3/bin/postgres -D /home/dev/postgres_data
postgres 18889  0.0  0.0  60060  1180 ?        Ss   16:39   0:00 postgres: logger process
postgres 18891  0.0  0.0 206480  1376 ?        Ss   16:39   0:00 postgres: checkpointer process

But when I connect from the command line, I get an error I've never seen before:

$ psql
psql: could not connect to server: No such file or directory
    Is the server running locally and accepting
    connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"?

/var/run/postgresql/ doesn't exist. When I search for that error I only get hits for Mac OS X but I'm on Ubuntu 12.04. The closest I can get is:

$ psql -h localhost -U postgres
psql: FATAL:  password authentication failed for user "postgres"
password retrieved from file "/home/dev/.pgpass"

I've tried adding passwords to /home/dev/.pgpass (there's really only 4 that I use on this system) and none of them are allowing me access. I've tried connecting with pgAdmin with the same result. I've restarted the server with sudo /etc/init.d/postgresql-9.3 restart and then logging in, didn't help.

I'm at a loss for what to do next.

Postgres 9.3, Ubuntu 12.

I think there are two unrelated problems here:

1) failure to connect on Unix domain socket

This part of ps output:

  /opt/PostgreSQL/9.3/bin/postgres -D /home/dev/postgres_data

indicates that you're not running postgresql as packaged for Ubuntu. Ubuntu doesn't install its binaries inside /opt (it doesn't even create /opt) and your data directory /home/dev/postgres_data is non-standard too.

The usual suspect is a postgres server installed with the EntrepriseDB installer instead.

/var/run/postgresql is the default unix_socket_directory of Ubuntu's postgres, not Entreprisedb's or any other. Using the psql command-line tool shipping with Ubuntu against a non-Ubuntu leads to this error you mentioned:

  psql: could not connect to server: No such file or directory
      Is the server running locally and accepting
      connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"?

This mismatch is frequent on Mac OS X because there are quite a few incompatible PostgreSQL installers for it and Apple ships with a client-side postgresql that's not configured for any of them.

Short of getting rid of EntrepriseDB's postgres and installing Ubuntu's (good idea for the future but overkill for just that problem), you need to locate your unix_socket_directory, typically by consulting your postgresql.conf, which should be inside the data directory: /home/dev/postgres_data according to ps.

Then give it as the -h argument to psql, this will be tried instead of /var/run/postgresql. Per the manual page:

 -h hostname, --host=hostname
           Specifies the host name of the machine on which the server is
           running. If the value begins with a slash, it is used as the
           directory for the Unix-domain socket.

2) bad postgres password

If you successfully changed a db password with pgAdmin but can't use it, it may be that you're experiencing a known pgAdmin bug.

See PostgreSQL user can not connect to server after changing password


sqlite3 - table_info looks messy I created a new database by using the command line binary on Suse.
After activating headers by using .headers ON it still looks messy when getting table information with PRAGMA table_info(TABLE1);:

cid|name|type|notnull|dflt_value|pk
0|COLUMN1|REAL|1||0

How can I achieve a cleaner look like this:

sqlite> pragma table_info(member);
cid      name          type     notnull  dflt_value  pk
-------  ------------  -------  -------  ----------  -------
0        account_id    text     1                    0
1        account_name  text     1                    0

In the sqlite3 command-line shell, this output format can be selected with .mode column.


Theory of indexes for query with joins I found the following talk by Zardosht Kasheff enlightening:

http://www.infoq.com/presentations/Index-SQL

but it only covers the case of simple queries on a single table.

Is there something similar for queries involving multiple tables?  Possibly including GROUP BY?

I'm looking for some theory/guidelines which I can apply to MySQL with the InnoDB storage engine.

A common mistake is

SELECT ... SUM(a.foo) ...
    FROM a
    JOIN b ON ...
    WHERE ...
    GROUP BY a.id

When you JOIN two tables, you usually get an explosion of the number of rows.  The SUM is then done, probably summing up a.foo repeatedly, leading to the 'wrong' (too big) answer.

But that was not what you asked.

A JOIN is almost always executed this way:

Pick a table to start with.  Apply any filtering (WHERE).  So far, the single-table rules apply.
Move on to another table.  But this time, use "Nested Loop Join".  This means "foreach row from step 1, reach into table 2".

So, the new question you are asking is how to reach into table 2?  There is an ON clause (assuming it is not a "cross join").  That gives you one (or more) field that should be used in the 'best' index for table 2.  Then use that field, plus any other fields in WHERE, together with the single-table rules to construct the optimal index for that JOIN.

Since the optimizer might pick a different order of tables, or might like to have multiple choices, find the best indexes for each order.

Then simplify.

INDEX(x), INDEX(x,y) --> INDEX(x,y)
Don't have indexes with more than, say, 5 fields.
If you are joining to the PRIMARY KEY of table 2, you don't need a longer index.
Consider "covering" indexes.  (EXPLAIN will say "Using index", not to be confused with "Using index condition", which is ICP.)

Here are some tips on how the optimizer thinks when joining two tables:

A table with a WHERE filter will be chosen before one without.
Other things being 'equal', the smaller table will be chosen first.
Sometimes an ORDER BY will be preferred over a WHERE in ordering the tables.
LIMIT is rarely a factor, although it probably should be.

My cookbook also focuses on single-table; maybe this answer will help me write about JOINs.


Update stats w/full scan on SQL Server 2014 uses 100% cpu, on 2008 R2, 15% Why does full scan update statistics use 100% of CPU on SQL Server 2014 when it uses maybe 20% of CPU on SQL Server 2008 R2, for the same tables, with similar hardware capability?

I have been looking at MAXDOP, other options, and really see nothing that stands out. I realize that there could be settings that could cause this, but the settings are very similar for both databases (for example, MAXDOP is 4 for both, with both having multiple cores). Both are Enterprise Edition.

Is there something "different" in SQL Server 2014 versus SQL Server 2008 R2 that could explain this?  I have the memory option at 90% for both servers.  Any thoughts on what to look for?

I run update statistics with full (100%) scan once a week on two servers using SQL Server 2008 R2/SP3 and SQL Server 2014/SP2, and the databases have the same structure.  On the 2008 R2 server the update stats of two very large tables takes several hours, which is what I expect, but the CPU stays under 20% or so utilization the entire time. On the 2014 server, though, the CPU goes to 100% for about 40 minutes. The tables are a bit smaller on the 2014 server. I see this by using SQL Monitor analysis menus.

Here is the output of the Ola log file on the 2014 SQL Server, CPU goes to 100% from about 2:10 to 2:45:

Date and time: 2017-06-24 02:10:20
Command: UPDATE STATISTICS [InVA].[dbo].[AuditField] [_WA_Sys_00000005_15502E78] WITH FULLSCAN
Outcome: Succeeded
Duration: 00:07:48
Date and time: 2017-06-24 02:18:08

Date and time: 2017-06-24 02:18:08
Command: UPDATE STATISTICS [InVA].[dbo].[AuditField] [_WA_Sys_00000006_15502E78] WITH FULLSCAN
Outcome: Succeeded
Duration: 00:32:22
Date and time: 2017-06-24 02:50:30

Here is the output of the Ola log file on the 2008 R2 SQL Server for the two stats above, but CPU goes to maybe 15%:

Date and time: 2017-06-24 03:30:32
Command: UPDATE STATISTICS [InGA].[dbo].[AuditField] [_WA_Sys_00000003_0425A276] WITH FULLSCAN
Outcome: Succeeded
Duration: 00:05:00
Date and time: 2017-06-24 03:35:32

Date and time: 2017-06-24 03:35:32
Command: UPDATE STATISTICS [InGA].[dbo].[AuditField] [_WA_Sys_00000004_0425A276] WITH FULLSCAN
Outcome: Succeeded
Duration: 00:52:31
Date and time: 2017-06-24 04:28:03

I cannot run them with server maxdop=1 since that eliminates all parallel plan generation, and that could hurt the application.  I plan to go the opposite direction and increase it to 8 (there are 16 cores on the box) and see what happens.  May go faster to reduce the length of time the CPU is pegged.  This job runs while users are mostly gone.

Statistics updates can go parallel based on a lot of different options in SQL Server:

Cost Threshold for Parallelism - a query must be this high to ride the parallelism train. Your two servers could have different CTFP settings that cause the 2008R2 update to go single-threaded, whereas the 2014 one can go multi-threaded.
Max Degree of Parallelism - dictates how many cores a query can use, at most, if SQL Server decides to parallelize it that far. The 2008R2 box might have MAXDOP set to 1, whereas the 2014 box might have it set to the default of 0 (unlimited.)
Resource Governor - this Enterprise Edition feature lets you throttle different groups of users or applications to different MAXDOPs.

In later versions of SQL Server (2016 & newer), this gets even more complicated:

Database-level scoped options - you can right-click on a database, go into properties, and set the MAXDOP level for that database.
Statistics parallelism hints - starting in 2016 SP2, the statistics creation & update statements accept MAXDOP hints

As you noted, your 2008R2 one is going single-threaded, whereas the 2014 one is going multi-threaded (thus finishing faster, but maxing out CPU while it runs.)

To find the right balance for your stats jobs, think about:

What other workloads are happening in the database at the same time? Can you afford to dominate the box during brief periods? For example, in data warehouses that sit idle during most weekend hours, I've seen folks crunch hard on stats updates with fullscans when they know nobody's using the server anyway. In heavy-duty transactional environments, you have to start using less impact for maintenance tasks if users complain even during midnight periods.
Is fullscan really necessary? Are you seeing queries that only get good plans when you use the fullscan option, or are you merely doing it as a best-practices thing? As your database grows, if your hardware investments don't keep pace, you may have to start making tradeoffs in stats sampling rather than doing fullscans.
Can you update stats less often? For example, update 1/4 of your stats each weekend, and then every month, everything will get stats updates?
Can you update less objects? Often I see folks updating stats even on huge audit or archive tables simply because a few dozen new inserts were done, but those inserts don't really affect the stats on the table (and nobody's querying it anyway.)


"SELECT TOP N" returns the wrong results Supposing I have a table foo similar to:

ident int, primaryKey, auto_increment
someNumber int
statusId int
...

where the someNumber column is, except for a few special cases, equal to ident.  (in those special cases, someNumber is always equal to a previous ident).

I'm seeing problems which give the appearance that a query such as SELECT TOP 10 ident, someNumber FROM foo WHERE statusId=@desiredStatusId ORDER BY someNumber will return the wrong 10 records.  The records all have the correct statusId, but randomly there is one whose someNumber is way off.  For eaxmple:

ident        someNumber
-------      ----------
1234567      1234567
1234700      1234700
1234568      1234568
1234569      1234569
1234570      1234570
...          ...

What could be causing this query to intermittently return the wrong results?

A corrupted index could do it.  Have you run DBCC CHECKDB against that database?


How to replace a CLOB column by a varchar2(4000) column? The following code seems to do the job, but I doubt, that it is robust enough.

SET serveroutput on

DECLARE
    v_isclob PLS_INTEGER;
    v_cmd    VARCHAR2(4000);
BEGIN
    SELECT CASE when exists (select * from USER_TAB_COLUMNS where COLUMN_NAME = 'Column_to_modify' AND TABLE_NAME = 'MyTable' AND DATA_TYPE = 'CLOB') then 1 else 0 end into v_isclob FROM DUAL;
    IF v_isclob > 0 THEN
        BEGIN
            EXECUTE IMMEDIATE 'ALTER TABLE MyTable RENAME COLUMN Column_to_modify  TO XXX';

            EXECUTE IMMEDIATE 'ALTER TABLE MyTable ADD Column_to_modify varchar2(4000)';

            EXECUTE IMMEDIATE 'UPDATE MyTable SET Column_to_modify = SUBSTR(XXX, 1, 4000)';

            EXECUTE IMMEDIATE 'ALTER TABLE MyTable DROP COLUMN XXX';
            dbms_output.put_line ('++    Column_to_modify modified to varchar2(4000)');
        EXCEPTION WHEN OTHERS THen
            dbms_output.put_line ('##### Error while modifying Column_to_modify');
        END;
    else
        dbms_output.put_line ('     Column_to_modify is varchar(4000)');
    END IF;
END;
/

How can I improve the code?

I would suggest adding a temp column rather than renaming the existing one. In Oracle 10g I created a test table:

CREATE TABLE  "TEST"
   (    "A" CLOB
   )

I then inserted data so that the results of length calls are as follows:

select length(a) from test

Result:

"LENGTH(A)"
12308
12308
12308
12308
12308

I then altered the table as follows:

alter table "TEST" modify
("A" VARCHAR2(4000) NOT NULL)
/

As expected receive the following error:
ORA-22859: invalid modification of columns

Change the column using the following script:

ALTER TABLE TEST ADD temp VARCHAR2(4000);
UPDATE TEST
   SET temp = DBMS_LOB.SUBSTR (a, 4000),
       a = NULL;

ALTER TABLE TEST DROP COLUMN A;
ALTER TABLE TEST RENAME COLUMN temp TO A;

Reissue the length sql:

"LENGTH(A)"
4000
4000
4000
4000
4000


How to automatically grant permissions to application created databases in SQL Server 2008 R2? So I have an application that automatically creates a new database after the database it is currently in reaches a fixed number of rows. I need to be able to automatically assign a user within the installation the db_datareader role for the automatically created database. I'm kind of at a loss here, any help is greatly appreciated.

You can do this by granting the permission in the Model database.  This database is used to create any new ones.

USE model
GO

CREATE USER UserName FROM LOGIN LoginName
GO

EXEC sp_addrolemember 'db_datareader','UserName'
GO

That way, as the application creates new databases (which are copied from Model), they will already have the User and the role membership.

However be warned.  This will be the case for ANY new database created with the CREATE DATABASE command on this instance.  So if you create a database for a different application it will already have this user and the user will have db_datareader. Databases created with ATTACH DATABASE or RESTORE DATABASE will not use the model database, and will not copy the security from the Model database.


How can I prove NOLOCK is the source of deadlocking problems? I'm not trying to start a windows/mac type discussion.

Personally, I don't need any convincing that NOLOCK is not a good idea as a reflexive practice. It seems when you're developing everything should be purposeful not reactionary (/amen)

So... programmer-in-charge insists NOLOCK is the way to go. Recommends with all ad-hoc queries and whenever querying production. I haven't seen a stored procedure without nolock hints on every table.

Don't want to be that guy who comes in and tells everyone a core belief is all wrong without something to back it up.

Just looking at the comment sessions under the various blog posts, sending a link may not be enough. Long-held beliefs etc... Some people aren't convinced it's a problem. See: Comments section under every nolock blog post I've read.

Currently some other DBAs are wrestling with some mysterious deadlocking. How does one determine whether NOLOCKs are the source?

It's been suggested looking at XML from traces etc, but this won't explicitly state that the deadlocks are causing the problem, will it? I've never seen error messages that straight forward. is that true?

How else could these deadlocks be pinned on this?

DDL statements like CREATE would be a clue. Is there any output that I could point to or some piece of data I could find that would help corroborate my theory before I raise the alarm?

Or am I running trace flags or extended events to identify what's running when the deadlocks occur and then deducing from DDL statements?

Looking at all the different ways data can get messed up with nolock hints, it seems like a hard problem to decisively pin down.

The architect-in-charge how insists NOLOCK is the way to go. Recommends with all ad-hoc queries and whenever querying production. I haven't seen a sproc without nolock hints on every table.

Sorry to hear that. This is pretty well universally regarded as an anti-pattern among expert SQL Server practitioners, but there may be nothing you can do to change the facts on the ground if the practice is truly ingrained, and based on one person's sincerely and deeply-held beliefs.

The question says that "sending a link may not be enough", but it's unclear what you want instead. We could write the most compelling argument in the world in an answer here, and you would still be left "sending a link" to it. Ultimately, no one here can know which set of arguments will be successful in your specific situation (if any).

Nevertheless, the following cover most points that some people find persuasive enough to change a previously common practice:

Bad habits : Putting NOLOCK everywhere by Aaron Bertrand
SQL Server NOLOCK Hint & other poor ideas By David Lean (Microsoft)
The Read Uncommitted Isolation Level by me
Justify NOT using the (nolock) hint in every query (related Q & A here)

Even so, you may not be able to 'win' this battle. I have worked in an environment that did this, understood the risks and reasons not to do it, but continued with it anyway. These were bright, logical people, but in the end it was an environment I could not be happy working in.

  Currently some other DBAs are wrestling with some mysterious deadlocking. How does one determine that NOLOCKs are the source.

The more usual pattern (perhaps more prevalent in the past) in that NOLOCK hints are introduced in an attempt to reduce the incidence of deadlocks. This can 'work' in that reducing the number of shared locks taken naturally reduces the chances of incompatible locks being taken, but it is not a good solution to the underlying problem, and comes with all the caveats noted in the previous section.

Nevertheless, NOLOCK hints can introduce new ways to deadlock. Using read uncommitted isolation can remove a transient blocking condition (resolved when the contended resource becomes available) into an unresolvable deadlock (where neither waiter can make progress) simply because the contention now happens at a different point, where e.g. write operations in a different order overlap. Dave Ballantyne has an example here:

Increasing deadlocks with NOLOCK

For more general advice about dealing with deadlocks, I recommend the following as a starting point:

The Difficulty with Deadlocks by Jeremiah Peschka

You should also familiarise yourself with the documentation:

Deadlocking (and all three sub-topics there)

Other useful resources:

SQL Server Deadlocks by Example by Gail Shaw
Deadlock troubleshooting by Bart Duncan


Efficiently implementing an orders table? Orders are typically handled with one large orders database and then status is changed as necessary "active", "fulfilled", etc.

However, my project is scaling quite quickly so we're getting a lot of queries of the orders table and I'm not sure if this is the best way to do it as it continues to grow - as customer service systems, monitoring systems and user-facing order systems are all querying the same table.

I'm thinking about creating an  ACTIVE_ORDERS / OLD_ORDERS table (i.e. certain systems would hit ACTIVE_ORDERS more often and others could hit OLD_ORDERS more often) and the location of these orders could be stored in a bridge table, and their interchange between tables controlled by a transaction.

I suspect I will be making certain tasks quite difficult, for example searching through ALL (active/old) orders with customer name LIKE something might be very slow unless collated through a temporary view.

What do you think?

Multiple tables and Partitioning would have similar performance -- DELETE from one place (table or partition) and INSERT into another.

The syntax for partitioning -- if you PARTITION BY LIST(status) -- is a simple UPDATE ... SET status = '...' WHERE ...;.  But the cost is effectively delete+insert.

But... What is the benefit?  Do you usually do SELECTs that say WHERE status = 'active'?  (or FROM ActiveOrders if using multi-table)?  If so, the performance is probably the same.

But, but... With a single table, INDEX(status, etc) works even slightly better for finding SELECT ... WHERE status = 'active' and etc;

Bottom line:  There are pros and cons of each approach.  Some of the pros and cons show up in the schema, some in the SQL you need to write.

Recommendation:

Start with a single table.
Six months from now, re-open the question.  By then, you will have several schema changes to make and you will be itching to rewrite the SQL.  At that time, do some experimenting with the 3 options (keep single table, use multiple tables, and use partitioning).

It should be possible to write a small number of SQL statements to mass-change from the old schema to the new.


Is there a way to associate a description with database snapshot? Is there a way to associate a description with database snapshot?

I usually name snapshots using original database name and timestamp:

DBName-05-27-2016_1018

I would like to be able to associate a comment like "Before executing schema update scripts related to feature NNN" with a snapshot.

This is something that I could put in database extended properties but since snapshot is read-only it is not possible after it is created and it doesn't look like extended properties can be specified as a part of snapshot creation. Making these comments a part of snapshot name is ugly.

Is there any other way to associate a metadata with database snapshot when it is created? Of course I can maintain a table in separate utility database just for this but this is not too convenient.

Is there any other way to associate a metadata with database snapshot when it is created?

No. Since a database snapshot is read-only, you cannot modify or add any settings to it. In-fact, extended properties cannot be applied to it.

So your best option is to have a table in the database (or a utility database) called e.g. dbo.database_snapshot_history with columns like servername, databasename, snapshotname, createBY, createdate


Tools and methodologies to keep to DBs aligned 2 DBs having schemas that represent the same semantic objects.
The first one is production DB (Non-RDBMS, in-house implemented in-memory DB with shitload of RAM). Other is Postgres.

Once in a while the production DB is changed (schema upgrade).

Question: what is the best practice to keep DBs of different types aligned semantically?

keep all SQL DDL in "patch" files in source control.

add a "schema_version" table to your database and track the latest patch file you are on.

write a script that runs new patches against your dbs.


Mongodb background indexing - no progress I have added a couple of background indexes on few collections on mongodb replicaset (3.0.2, MMAPv1 storage), and since then I'm seeing following entry in db.currentOp(true) response in all nodes (this one from Primary):

{
    "desc" : "rsSync",
    "threadId" : "0x2923ee0",
    "opid" : 5653,
    "active" : false,
    "op" : "none",
    "ns" : "local.replset.minvalid",
    "query" : {

    },
    "msg" : "Index Bulk Build: (3/3) btree-middle",
    "numYields" : 0,
    "locks" : {

    },
    "waitingForLock" : false,
    "lockStats" : {
        "Global" : {
            "acquireCount" : {
                "r" : NumberLong(2),
                "R" : NumberLong(1),
                "W" : NumberLong(1)
            }
        },
        "MMAPV1Journal" : {
            "acquireCount" : {
                "r" : NumberLong(1),
                "w" : NumberLong(1)
            }
        },
        "Database" : {
            "acquireCount" : {
                "r" : NumberLong(1),
                "R" : NumberLong(1)
            }
        },
        "Collection" : {
            "acquireCount" : {
                "R" : NumberLong(1)
            }
        }
    }
},

It has been running for nearly 5 days now. From "Index Bulk Build: (3/3) btree-middle" I would guess it's still rebuilding indexes, but it seems to be taking unusually long time. Whole database is ~140Gb on disk, and I wasn't adding indexes on biggest collections either.

Database performance is 2-3 times slower than it was before adding background indexes.

One of collections that I added index on has many slow (30ms) "count" queries (had them before new indexes).

Servers are idling, resource usage (CPU load, Disk I/O, Network traffic, RAM usage) looks unchanged in graphs. 3 bare metal machines in use, one Primary and 2 Secondary nodes. Rotational disks are used, but 128G RAM, 122G cached.

Database is doing ~25K RPM, collections that indexes were added upon are in use. Collection sizes that I added background indexes on vary from 100-1K to 100K-1M records.

How to tell what exactly mongodb is doing right now, and if it's possible / safe to kill this indexing operation if it's stuck?

Or perhaps indexing is over, but that message does not have to disappear, and my indexes somehow degraded the performance?

How to troubleshoot this situation?

Solved this one. While indexing was running, new code was released in production, which contained a bad query. I was checking Primary performance only, and killer query was happening on Secondary node. After adding one more necessary index, everything went back to normal, performance is stellar again.

However message "msg" : "Index Bulk Build: (3/3) btree-middle" is still there.