Clustered Index Scan when using greater than in WHERE clause I have the following table

create table t1(
col1 varchar(255) NOT NULL,
col2 varchar(255) NULL,
col3 bigint NULL,
CONSTRAINT PK_t1 PRIMARY KEY CLUSTERED
 (
   col1 asc
 )
)

CREATE NONCLUSTERED INDEX I_col3 ON t1(col3 desc)

This table has ~10000 rows with col2 always being populated and col3 has varying percentages of rows that are NOT NULL.

I am running the following query

DECLARE @number bigint
SET @number = 123456
SELECT col1, col2 FROM t1
WHERE col3 > @number

SQL is always generating a execution plan with a clustered index scan.

Now if I run the query as an adhoc query SQl does an index seek on I_col3 with a key lookup

SELECT col1, col2 FROM t1
WHERE col3 > 123456

The value being passed in the WHERE clause results in a small number of rows being returned (e.g. 3)

When running the ad-hoc query the execution plan shows the Estimated Number of Rows as 3.32. However, when I run the parameterized query it shows 2796 estimated rows.

If I add an index hint to the ad-hoc query it still shows 2796 estimated rows (not that I expect this to change) but it does do an index seek. When comparing the number of logical reads between the ad-hoc and parameterized query the
Parameterized Query:
Scan count 1, logical reads 89, physical reads 1, read-ahead reads 64, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Ad-Hoc Query
Scan count 1, logical reads 8, physical reads 1, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

And when forcing the index using the index hint
Scan count 1, logical reads 12, physical reads 1, read-ahead reads 18, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

I'm currently running SQl 2014 CP1-CU5. I've tried using trace flag 9184 to force SQL to use the old cardinality estimator which reduces the estimated rows to 1627 but still does a clustered index scan. I've also tried on SQL 2008 R2 and it has the same behavior as SQL 2014 with trace flag 9184.

In between each iteration of the query I am clearing the procedure cache and buffer pool.

I should also noted that I have also tried the following

Filtered INDEX on col3 WHERE NOT NULL
Filtered STATS on col3 WHERE NOT NULL

I've also done a full rebuild on the both the clustered and non clustered indexes and verified that stats are up to date.

Any suggestions or ideas?

Thanks again

UPDATE:
From reading through some links from Martin's answer I found the following blog post:

http://www.sqlskills.com/blogs/kimberly/exec-and-sp_executesql-how-are-they-different/

I confirmed that using sp_executesql or exec(@sql) does result in an index seek execution plan being generated and reused regardless of what the parameter value(s) are.

DECLARE @sql nvarchar(max)
SELECT @sql = 'SELECT col1, col2, FROM t1 WHERE col3 > @number'
EXEC sp_executesql @sql, N'@number bigint', @number = 123456789

The values of variables aren't generally sniffed so it will just assume a flat 30% of the table will be returned for that greater than predicate against an unknown value (cf. Selectivity Guesses in absence of Statistics).

When you use the literal it can look up the known value in the column statistics to get a much more accurate estimate.

If it estimates such a high percentage of the table will match it is exceedingly unlikely to give you a plan with lookups (the exact tipping point depends on how wide the table and index is).

If you use option (recompile) the variable value is able to be sniffed however...

DECLARE @number bigint
SET @number = 123456
SELECT col1, col2
FROM t1
WHERE col3 > @number
OPTION (RECOMPILE);

Will likely give you the same plan as when using the literal.


SQLCMD external editor I have set the environment variable SQLCMDEDITOR=gvim to invoke an vim with the ED command of SQLCMD. From my old Windows 7 computer, it used to work.

I have now a new laptop (windows 10) and, when attempting the same thing I get, on the same server:

C:\>sqlcmd -S <serverName>
1> SELECT GETDATE()
2> GO

-----------------------
2019-12-13 09:11:47.493

(1 rows affected)
1> ED
Sqlcmd: Error: Internal error at ExecProcess (Reason: The filename, directory name, or volume label syntax is incorrect).

I have checked that gvim is in the PATH. I have tried to set the variable to: gvim; gvim.exe; C:\Progra~2\Vim\vim81\gvim.exe; notepad.exe. Always the same.

Why is that ?

This looks like a SQLCMD bug. I can repro with SQLCMD 14.0.3026.27 on Windows 10.

I submitted the bug report on the SQL Server feedback site. Consider upvoting the bug report.


how to retrieve rows of different dates using IN clause My table structure:

+-----------------+--------------+------+-----+-------------------+----------------+
| Field           | Type         | Null | Key | Default           | Extra          |
+-----------------+--------------+------+-----+-------------------+----------------+
| id              | int(11)      | NO   | PRI | NULL              | auto_increment |
| user            | varchar(100) | NO   |     | NULL              |                |
| uniq_name       | varchar(100) | NO   |     | NULL              |                |
| info_name       | varchar(100) | NO   |     | NULL              |                |
| delay_time      | int(11)      | NO   |     | NULL              |                |
| track_time      | timestamp    | NO   |     | CURRENT_TIMESTAMP |                |
+-----------------+--------------+------+-----+-------------------+----------------+

I have to retrieve data from table for past seven days, not summed one. Data for a specific date, for this right now I have to fired same query seven times using like operator:

SELECT COUNT(DISTINCT `user`) FROM `tracks` WHERE `track_time` LIKE '2012-11-12%';

Which is not a good way to do. I am trying to do myself, also discussed on forums , it might be done with IN operator or using date1 < date2 < ....date for past seven days. For this, i am using a function in PHP in which I am supplying a date parameter, but query will take lot of time to display result on web. Please have a look on this situation.

If you are doing a range of consecutive dates, there is no need to use the IN operator.

If you use this to compute midnight seven days ago

DATE(NOW()) - INTERVAL 7 DAY + INTERVAL 0 SECOND

here is what you get back:

mysql> SELECT DATE(NOW()) - INTERVAL 7 DAY + INTERVAL 0 SECOND;
+--------------------------------------------------+
| DATE(NOW()) - INTERVAL 7 DAY + INTERVAL 0 SECOND |
+--------------------------------------------------+
| 2012-11-21 00:00:00                              |
+--------------------------------------------------+
1 row in set (0.01 sec)

mysql>

Your query will look like this:

SELECT COUNT(DISTINCT `user`) FROM `tracks`
WHERE `track_time` >= ( DATE(NOW()) - INTERVAL 7 DAY + INTERVAL 0 SECOND );

You also need an index to support the WHERE and COUNT

ALTER TABLE tracks ADD INDEX track_time_user_ndx (track_time,user);

Then, the query should go faster.

If you are doing a range of consecutive dates from some earlier period, simply supply the first and last date from midnight to midnight. For example, for the range 2012-11-12 to 2012-11-19:

SELECT COUNT(DISTINCT `user`) FROM `tracks`
WHERE `track_time` >= '2012-11-12 00:00:00'
AND   `track_time` <  '2012-11-19 00:00:00';

If you are doing a range of non-consecutive dates, simply structure the query using UNION. For example, for dates 2012-11-12,2012-11-19,2012-11-26:

SELECT COUNT(DISTINCT `user`) FROM
(
    SELECT user,track_time FROM `tracks`
    WHERE `track_time` >= '2012-11-12 00:00:00'
    AND   `track_time` <= '2012-11-12 23:59:59'
    UNION
    SELECT user,track_time FROM `tracks`
    WHERE `track_time` >= '2012-11-19 00:00:00'
    AND   `track_time` <= '2012-11-19 23:59:59'
    UNION
    SELECT user,track_time FROM `tracks`
    WHERE `track_time` >= '2012-11-26 00:00:00'
    AND   `track_time` <= '2012-11-26 23:59:59'
) A;

Give it a Try !!!

UPDATE 2012-11-27 13:52

If you want count of distinct users for just one date 2012-11-12

SELECT COUNT(DISTINCT user) usercount FROM `tracks`
WHERE `track_time` >= '2012-11-12 00:00:00'
AND   `track_time` <= '2012-11-12 23:59:59';

If you want count of distinct users for 7 consecutive dates 2012-11-12-2012-11-18 (You cannot include 2012-11-19)

SELECT
    DATE(track_time) track_date,
    COUNT(DISTINCT user) usercount
FROM
(
    SELECT user,track_time FROM `tracks`
    WHERE `track_time` >= '2012-11-12 00:00:00'
    AND   `track_time` <  '2012-11-19 00:00:00'
) A GROUP BY DATE(track_time);

UPDATE 2012-11-27 14:41 EDT

If you want this dynamically done as of NOW(), try this:

SELECT
    DATE(track_time) track_date,
    COUNT(DISTINCT user) usercount
FROM
(
    SELECT user,track_time FROM `tracks`
    WHERE `track_time` >= ( DATE(NOW()) - INTERVAL 7 DAY + INTERVAL 0 SECOND )
) A GROUP BY DATE(track_time);


Think I have the right indexes and query, but MySQL Query is still slow table_1

id | column_1 | import_date

Indexes: Mutli-column key (multi_column) of import_date and column_1 in addition to an individual index for each column.

table_2

id | column_1 | column_2

Indexes: Multi-column key (multi_column) for column_1 and column_2 in addition to an individual index for each column.

i.e. both tables have two composite indexes (of the same name in this question) as well as separate indexes.

Query

I've tried flipping the query around and playing with using/forcing different indexes but it didn't seem to help.

SELECT table_1.import_date, count(*) AS count
FROM table_1
FORCE INDEX (import_date)
JOIN table_2 USE INDEX (multi_column) ON table_1.column_1 = table_2.column_1 AND column_2 = "Some String"
WHERE table_1.import_date >= '2016-05-26'
GROUP BY table_1.import_date
LIMIT 365

I tried adding in the LIMIT and a where clause to check where the date was greater than the same date last year just to try and prevent a large number of results and filter but neither seemed to improve the performance of the query, both tables have a large record set.

EXPLAIN

id  select_type table   partitions  type    possible_keys   key key_len ref rows    filtered    Extra
1   SIMPLE  table_1 NULL    range   multi_column,import_date    import_date 6   NULL    1932171 100.00  Using index condition; Using where
1   SIMPLE  table_2 NULL    ref multi_column    multi_column    126 table_1.column_1,const  117 100.00  Using index

Sorry for obfuscated table and column names, trying to keep it simple.

Original Explain without force/use:

id  select_type table   partitions  type    possible_keys   key key_len ref rows    filtered    Extra
1   SIMPLE  table_1 NULL    index   multi_column,column_1,import_date   import_date 69  NULL    365 100.00  Using where; Using index
1   SIMPLE  table_2 NULL    ref multi_column,column_1,column_2  multi_column    126 table_1.column_1,const  117 100.00  Using index

Row counts for each table are 3,937,236 and 4,503,551 respectively.

The query currently takes around 80s, how long should it take, I'm not sure but I imagined with indexes then hopefully under a second.

Running locally on my development machine just now, Mac OS X El Capitan, 2.2 GHz i7 16gb ram.

MySQL version 5.7.15

Both tables are using InnoDB.

This query seems to take it down to roughly 20 seconds:

select import_date, count(*) as count
from table_1
where exists (select * from table_2 where table_1.column_1 = table_2.column_1 and column_2 = 'Some String')
group by import_date

How about FORCE INDEX (import_date) to FORCE INDEX (multi_column)?


More efficient way to write multiple "AND"? Anyone know a way to write this in a different manner ?

SELECT tableA.NO
FROM tableA
WHERE tableA.NO IN (SELECT GRI_NO FROM tableBB WHERE GRO_NO =62)
  AND tableA.NO IN (SELECT GRI_NO FROM tableBB WHERE GRO_NO =50)
  AND tableA.NO IN (SELECT GRI_NO FROM tableBB WHERE GRO_NO =70)

I have many more AND to add and it become quite a long query.  Does an equivalent of WHERE GRO_NO IN (62, 50, 70) exist for AND?

Edit below:

The equivalence of sampleA1 is sampleA2 for the OR keyword.

sampleA1: WHERE GRO_NO IN (62, 50, 70)

sampleA2: WHERE GRO_NO =62 OR GRO_NO =50 OR GRO_NO =70)

Does an equivalent of sampleB2 exist for the AND keyword ?

sampleB2:WHERE GRO_NO =62 AND GRO_NO =50 AND GRO_NO =70)

Edit2: I'v remove performance tag since it was more related to clarity / syntax / shorter sql than DB performance.

Not sure if that's is faster than what you have, but you can try

SELECT tableA.NO
FROM tableA
WHERE tableA.NO
IN
(
   SELECT GRI_NO FROM tableBB WHERE GRO_NO IN (62,50,70)
   GROUP BY GRI_NO
   HAVING COUNT(DISTINCT GRO_NO) =3
)


mysql 100% usage on all cpu On mysql community 5.5 (centos 6), VM on hyper-v win 2008 r2, 4 CPU, 48 GB RAM

Which setting(s) that I would need to change to reduce cpu usage and get my web app (joomla) to respond faster without buying new hardware?

[mysql]

# CLIENT #
port                           = 3306
socket                         = /var/lib/mysql/mysql.sock

[mysqld]

# GENERAL #
user                           = mysql
default-storage-engine         = InnoDB
socket                         = /var/lib/mysql/mysql.sock
pid-file                       = /var/lib/mysql/mysql.pid

# MyISAM #
key-buffer-size                = 32M
myisam-recover                 = FORCE,BACKUP

# SAFETY #
max-allowed-packet             = 16M
max-connect-errors             = 1000000

# DATA STORAGE #
datadir                        = /var/lib/mysql/

# BINARY LOGGING #
log-bin                        = /var/lib/mysql/mysql-bin
expire-logs-days               = 14
sync-binlog                    = 1

# CACHES AND LIMITS #
tmp-table-size                 = 32M
max-heap-table-size            = 32M
query-cache-type               = 0
query-cache-size               = 0
max-connections                = 500
thread-cache-size              = 50
open-files-limit               = 65535
table-definition-cache         = 1024
table-open-cache               = 2048

# INNODB #
innodb-flush-method            = O_DIRECT
innodb-log-files-in-group      = 2
innodb-log-file-size           = 512M
innodb-flush-log-at-trx-commit = 1
innodb-file-per-table          = 1
innodb-buffer-pool-size        = 40G
innodb_read_io_threads  = 8
innodb_write_io_threads = 8
innodb_thread_concurrency = 0

# LOGGING #
log-error                      = /var/lib/mysql/mysql-error.log
log-queries-not-using-indexes  = 1
slow-query-log                 = 1
slow-query-log-file            = /var/lib/mysql/mysql-slow.log

symbolic-links=0

user=mysql

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

Use mysqltuner for some quick suggestions. At a glance it's easy to tell that you will be alerted for  high memory allocation!! Your innodb-buffer-pool is much higher WRT available memory.

About high CPUs, what does your processlist or slow query log tells you? Sort or long running queries, using tmp tables? Queries not using indexes? Make sure you have well indexed tables for respective queries.

Use explain to review and optimize your queries.


How to login with Windows Authentication mode after domain name changes in SQL Server? I am using SQL Servre 2008 R2. I recently changed my domain name and after that when I try to login to SSMS using Windows authentication, I get an error

  Login Failed For User UserName
  Microsoft Sql Server Error : 18456.

How to resolve it? Can somebody please help to get login in SSMS using Windows authentication mode again?

Thanks in advance

You should login with SQL authentication (with your sa account)
and run this query:

DROP LOGIN [OldDomain\UserName]
GO

CREATE LOGIN [NewDomain\UserName] FROM WINDOWS WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english]
GO

ALTER SERVER ROLE [sysadmin] ADD MEMBER [NewDomain\UserName]
GO

You will then be able to login with your new domain name (be sure you change the domain name and username in the code).

If you need to recover your sa password you can read this article
http://v-consult.be/2011/05/26/recover-sa-password-microsoft-sql-server-2008-r2/


is there a way to improve this query by not re calculating functions? cross apply? when I set up replication I like to take a count of each table in the publisher, then I go to the subscriber I do the same thing in order to compare both pictures.

this is the picture from the publisher:

in order to generate this picture I run the following query:

USE ORCASTG_CA18_Repl
go
SELECT
         Publication=P.name
        --,Publication_description=p.description
        ,p.immediate_sync
        ,p.allow_anonymous
        ,p.replicate_ddl
        ,the_schema=OBJECT_SCHEMA_NAME(a.objid)
        ,TableName = a.name
        ,DestinationServer=s.srvname
        ,DestinationDB=s.dest_db
        ,DestinationSchema= a.dest_owner
        ,DestinationTable = A.dest_table
        ,Radhe='exec sp_count ' +'''' + QUOTENAME(OBJECT_SCHEMA_NAME(a.objid)) + '.' + QUOTENAME(a.name) + ''''

FROM dbo.syspublications P

INNER JOIN dbo.sysarticles A
        ON P.pubid = A.pubid

INNER JOIN dbo.syssubscriptions s
        ON a.artid = s.artid

WHERE 1=1
  AND s.dest_db <> 'virtual'

and that produces the following script:

exec sp_count '[dbo].[repl_application_placement]'
exec sp_count '[dbo].[repl_ApplicationCalendarRequest]'
exec sp_count '[dbo].[repl_ApplicationChecklist]'
exec sp_count '[dbo].[repl_ApplicationFlightDetail]'
exec sp_count '[dbo].[repl_ApplicationFlightLegDetail]'
exec sp_count '[dbo].[repl_ApplicationFlightReference]'
exec sp_count '[dbo].[repl_ApplicationProfile]'
exec sp_count '[dbo].[repl_ApplicationRequestFlightOption]'
exec sp_count '[dbo].[repl_ApplicationSkill]'
exec sp_count '[dbo].[repl_ApplicationVisaDetail]'
exec sp_count '[dbo].[repl_camp_profile]'

My question is:

Is there a way I can calculate the following line without re-running those functions:

,Radhe='exec sp_count ' +'''' + QUOTENAME(OBJECT_SCHEMA_NAME(a.objid)) + '.' + QUOTENAME(a.name) + ''''

I thought about cross apply!

I beleive you want to store the results of the multiple procs in a single run.
If that's the case then

I am not sure about what are the columns returned by the procedure sp_count
I am assuming that it returns tablename,total_count

So your code will be like this

USE ORCASTG_CA18_Repl
go
DECLARE @sql VARCHAR(MAX);

CREATE TABLE #table_count (table_name varchar(128), table_count INT)

  SELECT  @sql=COALESCE(@sql+ '', '')
           + 'INSERT INTO #table exec sp_count '
           +'''' + QUOTENAME(OBJECT_SCHEMA_NAME(a.objid))
           + '.' + QUOTENAME(a.name) +';'+'''')

FROM dbo.syspublications P

INNER JOIN dbo.sysarticles A
        ON P.pubid = A.pubid

INNER JOIN dbo.syssubscriptions s
        ON a.artid = s.artid

WHERE 1=1
  AND s.dest_db <> 'virtual'

  EXEC (@sql)

  SELECT * FROM #table_count tc

Also note, I have not tested the code. If you find issues when running please feel free to edit it.

Edit 1:
You can also use  the below methods instead of COALESCE()

STRING_AGG() (2017 onwards)

XML string concatenation

GROUP_CONCAT CLR method (Github)

All these is mentioned in the Aaron bertrand's blog here


Seeking feedback on table relationships, general schema design I'm designing a database for a business that sells tickets to a variety of events worldwide. The app will be built with Rails using PostgreSQL.

Here's what I have so far, link to full size image

I'm mostly concerned with the relationship between EVENT_CATEGORIES / EVENT_SUB_CATEGORIES / TEAMS_ARTISTS / COMPETITIONS.

A typical path for a user to take on the app would be

Sports (event_category)  ---> Football (event_sub_category)  ---> Champions league (competitions)  ---> Arsenal (teams_artists), skipping competitions for events without competitions e.g, Concerts.

Will these relationships hold up in a production environment? Is there anything more optimal I'm overlooking? Any other tips in general would be welcome and appreciated.

Thanks!

Event Category and Sub-Category are classifications of events. They are your way of tagging events (i.e. meta-attributes).  The Event itself is a physical-thing/occurence (independent of you)  to which you are attaching these attributes.  Competition and competitors are also physical things- that are associated with the occurrence. When designing, it helps to be aware of what is a physical thing, and what is your design artifact.

I don't think Competition , or Competitors or Performers (if a non-sport event) should depend upon your classification.

** So I would take out the link between sub-category and competition. **

An Event has performers.  A performer can be an individual or a team.
An event will be associated with one or more performers.

A competition like UEFA-CL can be considered a collection of events (*Not the otherway around - as in your model *). Hence competition is independent (or rather, a higher level entity) compared to an event. An Event is kind of like the 'delivery' (or instance) of that competition at a particular time / place.

Hence, Competition --> has multiple (> 0) events --> has multiple (> 0) performers. (This is the way I would look at competitions).  So I would make competition above the Event (in the object heirarchy - not talking of inheritence here)

I can talk about UEFA-CL without referring to a specific event in its set. Competition is composed of (stable) teams.  The competition can span seasons - so you might even have search/drill-down criteria such as Soccer.  You need to setup your production so that you can do analysis easily on your data too. I wouldn't want to bury competition deep inside your heirarchy.


Is it possible to get the name of a procedure by it's plan handle or query plan hash if the execution plan is already cleared from the plan cache? I ran a home-brew trace for the length of 1 day that captured a few things that were running. The trace includes the specific query text, the parent entity text of that query, the Plan Handle, and the Query Plan Hash.

Usually I can use RedGate's SQL Search on the query text to find the entity I'm looking for but that was a no-go in this case.
Unfortunately that was a day ago when the trace ran, and when I check the sys.dm_exec_cached_plans DMV, nothing is turning up for my plan handle.

Are there any other ways I can backtrace this query?

Update: Here's how the Query Store is currently configured on the database in question. Note sure if it can be useful for my case?

If (and that's a big IF) you're running SQL Server 2019 (CTP 2.4) then you can get the last known ACTUAL execution plan for a previously cached query plan:

SELECT qps.query_plan
FROM   sys.dm_exec_procedure_stats AS ps
       CROSS APPLY sys.dm_exec_query_plan_stats(deps.plan_handle) AS qps
WHERE  ps.object_id = OBJECT_ID('dbo.spMyProcedure');


Can you separate schemas and objects with spaces and line feeds in T-SQL? When writing a query, can you separate schemas and objects with spaces and line feeds?

Yes, this is valid T-SQL:

SELECT
*
FROM
sys
.
databases
d
WHERE
d
.
name
=
'tempdb';

As is this:

SELECT * FROM sys . databases d WHERE d . name = 'tempdb';
GO

And they both work fine.


what is default balancing window in MongoDB I have read about balancing window in MongoDB document. I see mongo tell about how to modify balancing window but I don't see the default of balancing window. I see only one document about chunk size in collection settings in config database. Do you know the default of balancing window ?

The default balancing window is 24x7x365, which means balancer will always move chunks if its on


Does PostgreSQL ever retry failed transactions internally? I am writing an application in Python and PostgreSQL (using psycopg2) and in one section we are using the following pattern.

As separate python commands which I assume translates to separate calls to PostgreSQL

1) BEGIN

2) create temporary table AAA as select ... from ...

3) create temporary table BBB as select ... from AAA

4) create temporary table CCC as select ... from BBB

5) insert into YYY select ... from CCC

6) END

At no point do we ever read any data back from the transaction making it a blind write.
If this series of statements would cause a conflict with another transaction and fail would PostgreSQL restart it internally or would that revert to me the same as if I was mixing in select statements into the above code.

Would it make a difference if I converted all the temporary statements to common table expressions using the "with" construct? From the perspective of needing to do manual transaction restarts that is.

At what point if ever does PostgreSQL ever restart transactions internally, does multiple statements like that work? Will it work for a single statement as with "with"? Will it never work?

ps. I can not release the full code as it apart from being several pages long is the property of my employer.

AFAIK PostgreSQL does not retry failed transaction. Even if this would be the case you can never be 100% sure your transaction will succeed: coding your application with any DBMS assuming transaction will never fail in the database would be wrong.

the proof is in the pudding

I know that other database may in some very special cases retry statement (not transaction): for example Oracle see https://asktom.oracle.com/pls/asktom/f?p=100:11:0%3a%3a%3a%3aP11_QUESTION_ID:11504247549852#3950826900346316085.


Use domain username on SQL server reporting services installed in a server which is part of a Workgroup I have installed SQL Server 2012 on Windows Server 2008. This system is configured to be part of a Workgroup and all the users in our organisation are part of a domain. I am trying to use the reporting services that is part of SQL Server.

I was trying to give "System User" permission to a user who is part of the domain through the "New System Role Assignment" page of the "Report Manager". It keeps throwing the error "user name or group name is not recognized.(rsUnknownUserName)"
Is it because the server is in a workgroup or is there any other reason?

Well according to MSDN here:

  Group or User
  Type the name of a group or user account in your
  domain. If the report server is running under a local account, you
  must specify local groups or users. If the report server is running
  under a domain account, you must specify domain groups or users. Enter
  the account in this format: <domain>\<account>.

I would take that to mean you will not be able to do what you are wanting. A work around I would try is to create a local group, adding the domain group/user to it, and try to add that local group to the new system role. It has been a while since I have supported a mixed environment so I am not sure if you can add domain accounts to a workgroup computer. You might have to check with your AD admin for assistance on that.


Is it a waste to set innodb_buffer_pool_instances greater than the # of CPUs? I set the innodb_buffer_pool_size to 20GB on a server with 12 CPU cores. My full database is 11gb, however most of it is archived tables that are almost never used. The total queried data is around 3 gb, and the frequently queried data is ~1.25 gb.

What should I set the innodb_buffer_pool_instances to?

innodb_buffer_pool_size / total queried data = 6 pool instances
innodb_buffer_pool_size / frequently queried data = 16 pool instances

Normally I'd pick option #2, but logically it seems number of buffer pools that can be used at any one time is no more than the total number of CPU cores.

Is it a waste to set innodb_buffer_pool_instances to more than the # of CPU cores?

I don't think you will need to have too many buffer pools for you queried data because the size of the frequently queried data doesn't quite justify it. This does of course depend on the definition of "frequently".

The appropriate documentation you should be referencing the page on innodb buffer pools, here:

http://dev.mysql.com/doc/refman/5.5/en/innodb-multiple-buffer-pools.html

The numbers I'm focusing on here is

  the frequently queried data is ~1.25 gb.

My rule of thumb when trying to keep InnoDB buffers the right size is to keep them at or around 1GB a piece, in order to keep the list of blocks as short as possible but keeping the list of buffers from being too far fetched, this will always depend on your actual needs however.

This is in line with MySQL's recomendations:

  For best efficiency, specify a combination of innodb_buffer_pool_instances and innodb_buffer_pool_size so that each buffer pool instance is at least 1 gigabyte.

The point of the multiple buffer pools is to ensure your CPU threads don't meet high contention in accessing the data. Or as they put it:

  You might encounter bottlenecks from multiple threads trying to access the buffer pool at once. You can enable multiple buffer pools to minimize this contention.

However this feature is more for larger amounts of data being frequently accessed as opposed to the 1.25GB you're system uses frequently. Ultimately if I were in your position, I wouldn't see a need for having more buffer pools than the number of CPU's assuming all CPU's are only performing MySQL related tasks. I would also look into the affects of using innodb_old_blocks_time to prevent the occasional query of your archived tables from taking the place of a block of data that is used over and over again.

I hope that helps, let me know how everything works out.


Is it possible to have read/write race conditions in a percona cluster? I had a discussion today regarding an incident in a percona cluster. The setup was as follows:

A three node percona cluster load-balanced by a haproxy. Both reads and writes where balanced by the proxy to all three nodes.

The application (php) using the database, was coded as such, that it could perform a write on the database, and then immediately try to read the new record. The guy told me that there were cases that, if the immediate read (after the write) was balanced by the proxy to a different node than the one used for the write operation, it would not locate the record.

Is this possible in a percona cluster? As far as I know, the node that executes a write query, it must first receive confirmation by all percona nodes prior returning success to the client issuing the query. So how is this possible, even if the read query lands on a different server than the one used to perform the write operation?

A replication setting determines which types of queries first perform a causality check, i.e. have the executing server confirm that it is up-to-date.

Check the wsrep_sync_wait variable description here:
https://www.percona.com/doc/percona-xtradb-cluster/5.5/wsrep-system-index.html

Our consultant advised setting 6 (i.e. 2 | 4), meaning UPDATE, DELETE, INSERT, and UPDATE perform a causality check. In other words, write queries. In the end, we decided to switch to 7 (i.e. 1 | 2 | 4), performing causality checks on reads as well. Although this makes reads take longer, the problem of outdated reads that you describe goes away.

Note: After weeks of success with this setting, the problem suddenly came back. It disappeared as soon as we (temporarily) started doing all reads and writes on the same server, so something seems to be up. I'm wondering if there is another setting that comes into play and needs to be fixed as well.

Updated...

I believe we mitigated the issue in the previous note, but I can't seem to remember what exactly it was.

Use a single write node

In any case, I highly recommend using only a single write node. Since all writes are replicated anyway, all servers are doing comparable writes, so write scaling is severely limited - unlike read scaling. Writing on a single node, however, does give us certain advantages.

Avoid conflicting writes

Using a cluster with multiple write nodes has an unfortunate effect: if two simultaneous writes onto different nodes conflict, they may initially appear to succeed, but then one of them will fail at commit time. At least for application development, this has a significant impact. Every place in the application that runs this risk must implement some catch-and-retry logic... or we could just use a single write node!

Of course, you'll want high availability. We use ProxySQL to load balance. If the write node goes (or is taken) down, another node becomes the write node. Yes, only during failover there might be the risk described above, but that is only a very small window. Ideally, when doing a manual failover, we should momentarily queue incoming requests and let the old write node finish its work, before assigning a new write node.

Auto-increment is not always increasing

Auto-increment is not guaranteed to be always increasing! I found this a particularly disturbing issue that was unknown to many. The greatest ID is not necessarily the latest row. Counterintuitive and harder to reason about. There a various configuration options.

Auto-increment can be controlled using wsrep_auto_increment_control & friends, having ServerA use 1, 4, 7, ..., ServerB use 2, 5, 8, ..., and ServerC use 3, 6, 9, .... This avoids contention of IDs, yes, but it also skips IDs and really creates non-incremental sequences.

Alternatively, as I understand from Percona, we can use a single write node and disable auto-increment control. The values should now always be increasing. During a failover, the new write node might fail a transaction or two at commit time if it has not replicated the latest auto-increment value yet, although I expect this should be mitigated by the causality setting discussed at the top of this answer.

I would go with the latter, and, just to be sure, instruct everyone to not rely on auto-increment being strictly increasing.

Do let us know if you can achieve a single write node and high availability with HAProxy. I have seen it done with ProxySQL, but it would be great to know if it can be done without it.


Why does ALTER AUTHORIZATION on a table drop all permissions on the target? This fact is documented here:
ALTER AUTHORIZATION (Transact-SQL)

If the target entity is not a database and the entity is being
transferred to a new owner, all permissions on the target will be
dropped.

I had no idea about this until I faced the fact.
One day there was a need to give EXECUTE permission on every existing/future stored procedure to some user, this user is a member of db_datareader database role only and he should not be able to modify any data, but since the ownership chain in place once he has an EXECUTE permission he would be able to modify data through the stored procedures. So I had to break the ownership chain and generated a script that changed authorization on all the existing tables to some other user.
As soon as the authorization was changed, all the permissions on all the tables in that database disappeared. I checked the documentation mentioned above and found out that this behaviour is documented.
But it's quite unexpected (to me, at least) so I  wonder if someone has any explanation. Why did they do so? Why is there the need for dropping all the permissions on some table if the authorization on it was changed?

Because the new owner don't want to be given an object that a bunch of other people has access to?
Imagine buying a new house. Do you expect other folks to have access to your new house? Or would you say that it is up to you to hand out the keys/codes to your new house - to the people you trust?
It is hard to respond to "why" questions, since we'd have to been in the meeting when this was decided in order to hear the reasoning going back and forth. But above is my guess.
(I thought about this possibly being from the SQL standard, but it doesn't seem like there's an ALTER AUTHORIZATION command in ANSI/ISO SQL...?)


SQLServerLogMgr::LogWriter: Operating system error 1117 We have an ongoing issue with our production sql server (physical) where we randomly receive this error in the log which puts the database into a recovery state

 SQLServerLogMgr::LogWriter: Operating system error 1117(The request could not be performed because of an I/O device error.) encountered.

The issue always occurs on our drive storing the transaction logs. The database normally recovers on its own, but few instances it does not and we need to restart the instance to recover. No errors returned from any database for dbcc checkdb.

Our storage team has been investigating it for weeks with our vendor, but with no luck. The investigation is on-going.

That being said, how should the sql server dba handle this error aside from reporting it to the storage team and checking for database corruption? I am wondering if there is any more information I may be able to gather from the sql server side which may help in their investigation?

Running SQL Server 2012 SP3, storage is a SAN.

First Update

Our infrastructure team made the following changes last night

Updated the firmware on all NICs on the database server
Updated the firmware on the network switches
Enabled Jumbo frames for ICSCI

We haven't received the error yet, I'll update again in a week or so.

Second Update

The changes made in previous update did not resolve the issue. Last night we moved tempdb from the SAN to local drive on the physical server and we disabled iSCSI optimization connection tracking. We haven't received the error yet, and we also see much faster disk read/writes to our data and log drives (still on the san) and of course tempdb being local. Additionally, we were receiving many iSCSI errors in the windows event logs during the time of error and also throughout the day. Since these changes last night those iCSI errors are mostly gone, there are still some coming in but not nearly as much.

Thanks,
Kevin

That being said, how should the sql server dba handle this error aside from reporting it to the storage team and checking for database corruption?

There really isn't anything you can do from the database side. SQL Server is a victim of the underlying hardware and virtualization (if any) having issues. The underlying issue (driver, hardware, config, etc.) needs to be fixed. Note that if you're in a virtualized environment it could be a software layer in between or an issue with the host/guest config, etc., and not a physical hardware or storage issue.

Realistically, removing all the filter drivers and associated software, stopping and in-between layers by removing them and putting this on physical (if virtual) and/or changing storage solutions (for example, using local instead of remote/SAN) can help aid in troubleshooting the issue. Updating drivers (for example multipathing, device, firmware, etc.) could also be helpful but not something I would charge a DBA to do but a datacenter or systems admin.

  I am wondering if there is any more information I may be able to gather from the sql server side which may help in their investigation?

Not really. Underneath we're calling read and write API's through Windows. The return code from the API call through Windows is this Windows error code which we're bubbling up so that the admin of SQL Server knows why SQL Server is having issues.

If anything, since it's a single volume they should be able to isolate that on the backend and enable infrastructure tracing. If this is a physical machine this would be from the HBA/scsi controller and below through the hardware. If it's virtual, then from the Host through the same layers.

Sadly, this is easier to say than do and most places are ill equipped to actually investigate these style of issue - especially when the environment is virutalized.

  Final Thoughts

What do the system event logs say? Are there further NTFS or other corruption issues coming up? Are devices being reset? The system event log should be dissected with an extremely fine toothed comb to see if there are a series of events or items that seem to lead up to this or if it's spontaneous. Additionally I've found these events to generally cluster around certain items such as times of high use on a specific controller or overhwelmed SANs.


Forcing execution plan with local join hints I have several encounters with bad execution plans in batch jobs during a week, and to avoid forcing plans I have moved on to adding local join hints (when these join types are the difference between good and bad execution plans). This way I get SQL Server to choose most of the plan, while enforcing the few joins I know are necessary to be able to finish the queries.

In the execution plans below, I want to enforce the join types to be somewhat the same and will therefore use local joins hints also for these. However, I was wondering if I am able to trigger other actions in the execution plans as well, such as:

List item

SORT(Distinct Sort)
Stram Aggregate(Aggregate)

Are these actions something I can choose, or are they dependent on join types/order choosed during the query?

Both plans are created by XML extracted from Query Store.

Good execution plan: https://www.brentozar.com/pastetheplan/?id=HyYMn7K2V

Bad execution plan: https://www.brentozar.com/pastetheplan/?id=Hka6i7Yh4

I want to enforce the join types to be somewhat the same and will
  therefore use local joins hints also for these

Adding join hints should be a last resort. There should be ways to rewrite the query / add indexes to get a more consistent result.

These plans are also estimated execution plans, in this case only you knows how well / bad the actual query will perform.

If the issue is parameter sniffing, OPTION(RECOMPILE) would be the easiest solution.

Is the LEFT JOIN only used for filtering? A NOT EXISTS might work better to filter earlier.

Having said all that, with the limited information given, here are some possible, quick rewrites.

Rewrite #1 LEFT JOIN to NOT EXISTS

The OPTION(RECOMPILE)is added to get better estimates based on the parameters provided.

INSERT INTO dbo.cte_MDTForsikringssum
 SELECT DISTINCT
   mdtp.AvtaleNummer
  ,mdtp.MedlemskapNummer
  ,mdtp.Dekningstype
  ,mdtp.StartAlder
  ,mdtp.OpphorsAlder
  ,mdtp.PeriodeStartDato AS GjelderFraDato
  ,NULL GjelderTilDato
  ,mdtp.AjourholdDato AS EndretDato
  ,CONVERT(DECIMAL(18,8), 0) AS Forsikringssum
  ,0 AS Avkortningsfaktor
  ,0 AS PensjonsgivendeGrunnlag
  ,0 AS Folketrygd
  ,mdtp.Kjorenr_k
 FROM [BMPERSIST].vips.impMedlemskapDekningTrinnPremie_full mdtp

 INNER JOIN
  (SELECT
   AvtaleNummer,
   MedlemskapNummer,
   Dekningstype,
   StartAlder,
   OpphorsAlder,
   YEAR(PeriodeStartDato) AS PeriodeStartDatoAar,
   MONTH(PeriodeStartDato) AS PeriodeStartDatoManed,
   MAX(AjourholdDato) AS maxAjourholdDato
  FROM [BMPERSIST].vips.impMedlemskapDekningTrinnPremie_full
  WHERE --PeriodeStartDato < @dato--GETDATE()
   ( -- 27.03.2019 Endret WHERE betingelser lik neste step for å minske datamengden i TEMP tabell
    (PeriodeStartDato BETWEEN @StartDato AND @SluttDato)
    OR (Kjorenr_k = @kjorenr AND PeriodeStartDato < @dato)
   )
  AND KVID_Kontotype IN (189 --ArligInnskudd
        ,412,413 --AdmRes
        ,190,407,408,409,410,411,591) --Risiko
  GROUP BY
   AvtaleNummer,
   MedlemskapNummer,
   Dekningstype,
   StartAlder,
   OpphorsAlder,
   YEAR(PeriodeStartDato),
   MONTH(PeriodeStartDato)
  ) ajourholdD
 ON ajourholdD.AvtaleNummer = mdtp.AvtaleNummer
 AND ajourholdD.MedlemskapNummer = mdtp.MedlemskapNummer
 AND ajourholdD.Dekningstype = mdtp.Dekningstype
 AND ajourholdD.StartAlder = mdtp.StartAlder
 AND ajourholdD.OpphorsAlder = mdtp.OpphorsAlder
 AND ajourholdD.PeriodeStartDatoAar = YEAR(mdtp.PeriodeStartDato)
 AND ajourholdD.PeriodeStartDatoManed = MONTH(mdtp.PeriodeStartDato)
 AND ajourholdD.maxAjourholdDato = mdtp.AjourholdDato
 WHERE mdtp.PeriodeStartDato <= @dato
 AND NOT EXISTS
 (
 SELECT * FROM
 dbo.cte_MDTForsikringssum dest
 WHERE dest.AvtaleNummer = mdtp.AvtaleNummer
 AND dest.MedlemskapNummer = mdtp.MedlemskapNummer
 AND dest.Dekningstype = mdtp.Dekningstype
 AND dest.StartAlder = mdtp.StartAlder
 AND dest.OpphorsAlder = mdtp.OpphorsAlder
 AND dest.GjelderFraDato = mdtp.PeriodeStartDato
 AND dest.EndretDato = mdtp.AjourholdDato
 )
OPTION(RECOMPILE);

Rewrite #2 Also removing the OR by using UNION

   INSERT INTO dbo.cte_MDTForsikringssum
 SELECT DISTINCT
   mdtp.AvtaleNummer
  ,mdtp.MedlemskapNummer
  ,mdtp.Dekningstype
  ,mdtp.StartAlder
  ,mdtp.OpphorsAlder
  ,mdtp.PeriodeStartDato AS GjelderFraDato
  ,NULL GjelderTilDato
  ,mdtp.AjourholdDato AS EndretDato
  ,CONVERT(DECIMAL(18,8), 0) AS Forsikringssum
  ,0 AS Avkortningsfaktor
  ,0 AS PensjonsgivendeGrunnlag
  ,0 AS Folketrygd
  ,mdtp.Kjorenr_k
 FROM [BMPERSIST].vips.impMedlemskapDekningTrinnPremie_full mdtp
 INNER JOIN
  (
    SELECT
    AvtaleNummer,
    MedlemskapNummer,
    Dekningstype,
    StartAlder,
    OpphorsAlder,
    YEAR(PeriodeStartDato) AS PeriodeStartDatoAar,
    MONTH(PeriodeStartDato) AS PeriodeStartDatoManed,
    MAX(AjourholdDato) AS maxAjourholdDato
    FROM
    (
    SELECT
    AvtaleNummer,
    MedlemskapNummer,
    Dekningstype,
    StartAlder,
    OpphorsAlder,
    PeriodeStartDato,
    AjourholdDato
    FROM
    [BMPERSIST].vips.impMedlemskapDekningTrinnPremie_full
    WHERE --PeriodeStartDato < @dato--GETDATE()
    ( -- 27.03.2019 Endret WHERE betingelser lik neste step for å minske datamengden i TEMP tabell
    (PeriodeStartDato BETWEEN @StartDato AND @SluttDato)
    )
    AND KVID_Kontotype IN (189 --ArligInnskudd
        ,412,413 --AdmRes
        ,190,407,408,409,410,411,591) --Risiko
    UNION
    SELECT
    AvtaleNummer,
    MedlemskapNummer,
    Dekningstype,
    StartAlder,
    OpphorsAlder,
    PeriodeStartDato,
    AjourholdDato
    FROM [BMPERSIST].vips.impMedlemskapDekningTrinnPremie_full
    WHERE
    (Kjorenr_k = @kjorenr AND PeriodeStartDato < @dato)
    AND KVID_Kontotype IN (189 --ArligInnskudd
        ,412,413 --AdmRes
        ,190,407,408,409,410,411,591) --Risiko
  ) AS A
GROUP BY
AvtaleNummer,
MedlemskapNummer,
Dekningstype,
StartAlder,
OpphorsAlder,
YEAR(PeriodeStartDato),
MONTH(PeriodeStartDato)
 )
  ajourholdD
 ON ajourholdD.AvtaleNummer = mdtp.AvtaleNummer
 AND ajourholdD.MedlemskapNummer = mdtp.MedlemskapNummer
 AND ajourholdD.Dekningstype = mdtp.Dekningstype
 AND ajourholdD.StartAlder = mdtp.StartAlder
 AND ajourholdD.OpphorsAlder = mdtp.OpphorsAlder
 AND ajourholdD.PeriodeStartDatoAar = YEAR(mdtp.PeriodeStartDato)
 AND ajourholdD.PeriodeStartDatoManed = MONTH(mdtp.PeriodeStartDato)
 AND ajourholdD.maxAjourholdDato = mdtp.AjourholdDato
 WHERE mdtp.PeriodeStartDato <= @dato
 AND NOT EXISTS
 (
 SELECT * FROM
 dbo.cte_MDTForsikringssum dest
 WHERE dest.AvtaleNummer = mdtp.AvtaleNummer
 AND dest.MedlemskapNummer = mdtp.MedlemskapNummer
 AND dest.Dekningstype = mdtp.Dekningstype
 AND dest.StartAlder = mdtp.StartAlder
 AND dest.OpphorsAlder = mdtp.OpphorsAlder
 AND dest.GjelderFraDato = mdtp.PeriodeStartDato
 AND dest.EndretDato = mdtp.AjourholdDato
 )
OPTION(RECOMPILE);

Rewrite #3 Adding an additional temp table to store the inner join

By adding a temp table to split up the queries, the optimizer might get better estimates on the final query.

 SELECT
   AvtaleNummer,
   MedlemskapNummer,
   Dekningstype,
   StartAlder,
   OpphorsAlder,
   YEAR(PeriodeStartDato) AS PeriodeStartDatoAar,
   MONTH(PeriodeStartDato) AS PeriodeStartDatoManed,
   MAX(AjourholdDato) AS maxAjourholdDato
INTO #TEMP
    FROM
    (
    SELECT
    AvtaleNummer,
    MedlemskapNummer,
    Dekningstype,
    StartAlder,
    OpphorsAlder,
    PeriodeStartDato,
    AjourholdDato
    FROM
    [BMPERSIST].vips.impMedlemskapDekningTrinnPremie_full
    WHERE --PeriodeStartDato < @dato--GETDATE()
    ( -- 27.03.2019 Endret WHERE betingelser lik neste step for å minske datamengden i TEMP tabell
    (PeriodeStartDato BETWEEN @StartDato AND @SluttDato)
    )
    AND KVID_Kontotype IN (189 --ArligInnskudd
        ,412,413 --AdmRes
        ,190,407,408,409,410,411,591) --Risiko
    UNION
    SELECT
    AvtaleNummer,
    MedlemskapNummer,
    Dekningstype,
    StartAlder,
    OpphorsAlder,
    PeriodeStartDato,
    AjourholdDato
    FROM [BMPERSIST].vips.impMedlemskapDekningTrinnPremie_full
    WHERE
    (Kjorenr_k = @kjorenr AND PeriodeStartDato < @dato)
    AND KVID_Kontotype IN (189 --ArligInnskudd
        ,412,413 --AdmRes
        ,190,407,408,409,410,411,591) --Risiko
  ) AS A

INSERT INTO dbo.cte_MDTForsikringssum
 SELECT DISTINCT
   mdtp.AvtaleNummer
  ,mdtp.MedlemskapNummer
  ,mdtp.Dekningstype
  ,mdtp.StartAlder
  ,mdtp.OpphorsAlder
  ,mdtp.PeriodeStartDato AS GjelderFraDato
  ,NULL GjelderTilDato
  ,mdtp.AjourholdDato AS EndretDato
  ,CONVERT(DECIMAL(18,8), 0) AS Forsikringssum
  ,0 AS Avkortningsfaktor
  ,0 AS PensjonsgivendeGrunnlag
  ,0 AS Folketrygd
  ,mdtp.Kjorenr_k
 FROM [BMPERSIST].vips.impMedlemskapDekningTrinnPremie_full mdtp

 INNER JOIN
  (
  SELECT *
  FROM #TEMP
  ) ajourholdD
 ON ajourholdD.AvtaleNummer = mdtp.AvtaleNummer
 AND ajourholdD.MedlemskapNummer = mdtp.MedlemskapNummer
 AND ajourholdD.Dekningstype = mdtp.Dekningstype
 AND ajourholdD.StartAlder = mdtp.StartAlder
 AND ajourholdD.OpphorsAlder = mdtp.OpphorsAlder
 AND ajourholdD.PeriodeStartDatoAar = YEAR(mdtp.PeriodeStartDato)
 AND ajourholdD.PeriodeStartDatoManed = MONTH(mdtp.PeriodeStartDato)
 AND ajourholdD.maxAjourholdDato = mdtp.AjourholdDato
 WHERE mdtp.PeriodeStartDato <= @dato
 AND NOT EXISTS
 (
 SELECT * FROM
 dbo.cte_MDTForsikringssum dest
 WHERE dest.AvtaleNummer = mdtp.AvtaleNummer
 AND dest.MedlemskapNummer = mdtp.MedlemskapNummer
 AND dest.Dekningstype = mdtp.Dekningstype
 AND dest.StartAlder = mdtp.StartAlder
 AND dest.OpphorsAlder = mdtp.OpphorsAlder
 AND dest.GjelderFraDato = mdtp.PeriodeStartDato
 AND dest.EndretDato = mdtp.AjourholdDato
 )
OPTION(RECOMPILE);

DROP TABLE #TEMP;

Closing note

If there is more information, like the table definitions and some sample data, more can be done, these three rewrites simply look like the fastest and easiest wins at the time of writing.


Strange Database Mirroring Problem I am noticing a strange problem with DB mirroring on SQL Server 2012 (standard edition). The mirroring is set up, and successfully established, and working. There is no witness.
Once principal is rebooted, all the databases are stuck in recovery mode, thus making the DBs inaccessible. Same thing happens with mirror, when the server is restarted, the DBs are stuck in recovery. In order to get out of the recovery, the endpoint needs to be stopped and started again. I've checked the following:

Mirroring is working during normal setup.
Accounts have permissions to establish mirroring.
All DBs have owners that is sa.
SQL Server is running under domain account and the owner is sysadmin on the server. I also gave it explicit grant/authorization to the endpoint.
The error log (startup) says that the endpoint is started.

In order to make the DBs accessible again, the following code is executed:
ALTER ENDPOINT mirroring STATE = STOPPED

ALTER ENDPOINT mirroring STATE = STARTED

After executing the above query, DBs come back to normal state.
Startup error log:

2016-04-03 14:03:24.900   spid18s Database Mirroring Transport is disabled in the endpoint configuration.

2016-04-03 14:03:30.240   spid28s The Database Mirroring endpoint is now listening for connections.

Followed by Database Mirroring is Active with Database 'DatabaseName' as principal copy.
After the above code is executed:

2016-04-03 14:12:42.500   spid67  The Database Mirroring endpoint has stopped listening for connections.

because I am stopping it

2016-04-03 14:12:46.530   spid67  The Database Mirroring endpoint is now listening for connections.

because I am starting it
At this point, Database mirroring is active with database 'DatabaseName' as principal copy shows up again.
Thanks in advance.

I seriously suggest building another system with SQL Server Express and configure it as your witness server.  Then set your operating mode to "High safety with automatic failover (synchronous)".

This solution will alleviate you of this problem.  You will incur a cost for the desktop windows license, but as you may already know, SQL Server Express is of no cost monetarily.


Bulk insert M:N relation in PostgreSQL I need to import data from an old database to a new one, with slightly different structure. For example, in the old database, there is a table recording employees and their supervisors:

CREATE TABLE employee (ident TEXT PRIMARY KEY, name TEXT, supervisor_name TEXT)

Now, the new database is as follows:

CREATE TABLE person (id BIGSERIAL PRIMARY KEY, name TEXT, old_ident TEXT);
CREATE TABLE team (id BIGSERIAL PRIMARY KEY);
CREATE TABLE teammember (person_id BIGINT, team_id BIGINT, role CHAR(1));

That is, instead of plain table of employees with their supervisors' names, the new (more generic) database enables to create teams of people. The employees are members with role 'e', supervisors with role 's'.

The question is how to easily migrate the data from employee to the new structure, one team per employee-supervisor pair. For example, employees

employee: ('abc01', 'John', 'Dave'), ('abc02', 'Kyle', 'Emily')

are to be migrated as

person: (1, 'John', 'abc01'), (2, 'Dave', NULL), (3, 'Kyle', 'abc02'), (4, 'Emily', NULL)
team: (1), (2)
teammember: (1, 1, 'e'), (2, 1, 's'), (3, 2, 'e'), (4, 2, 's')

I would consider using a data-modifying CTE, inserting the employees and supervisors first, then teams among them. However, CTE may only return data from the inserted table row. Thus, I am not able to match who was the supervisor of who.

The only solution I can see is using plpgsql, which would simply iterate over the data, hold the inserted team IDs in a temporary variable, and then insert the appropriate teammember rows. But I am curious whether there are simpler or more elegant solutions.

There will be roughly several hundreds to several thousands of employees. Although it is generally a good practice, in my case, I wouldn't like to generate the new IDs based on the old ones, as the old IDs are strings like *.GM2. I store them to the old_ident column for reference.

You have all the information you need to populate the new database from the old one with 4 insert statements:

create table team_ids (id serial, name TEXT)

insert into team_ids (name)
select distinct supervisor_name from employee

-- now supervisors have ids assigned by "serial" type

insert into person (id, name, old_ident)
select ident, name, ident from employee
union
select ident, supervisor_name, ident from employee

insert into team (id) -- meh
select id from team_ids

insert into teammember (person_id, team_id, role)
select e.ident, t.id, 'e')
from employee as e, join team_ids as t
on t.name = e.supervisor_name
union -- and, I guess
select t.id, t.id, 'm')
from team_ids as t

You may have to adjust to taste.  I'm assuming employee.ident can be mapped onto person.id, and that your DBMS allows assigning values to columns with auto-generated values.  Except for that, it's just basic SQL, nothing fancy and, of course, no loops.

Additional commentary:

The 'team' table might be (more conventionally) renamed to department.
A SERIAL (with its 2 billion possibilities) should be plenty, no need for a BIGSERIAL.
There appears to be no database mechanism to enforce 1:1 cardinality of manager to team. Doesn't every team need a leader, by definition? Is there not a CHECK or FOREIGN KEY constraint for teammember.role? Perhaps the question simplified these details away.
The "teammember" table name would more conventionally have a word boundary, for example TeamMember or team_member.


Is it more efficient to subtract a value from a date or use DATEADD with a negative value I have a stored procedure that I need to make more efficient. It currently looks like

delete_more:
    DELETE TOP(500)
    FROM [dbo].[Foo]
    WHERE [DateCreated] < DATEADD(DAY, -1 * @MaxAgeInDays, GETDATE())

    IF @@ROWCOUNT > 0
        GOTO delete_more

The first step was to extract some variables

DECLARE @Now DATETIME = GETDATE()
DECLARE @EarliestDate DATETIME = DATEADD(DAY, -1 * @MaxAgeInDays, @Now)

delete_more:
    DELETE TOP(500)
    FROM [dbo].[Foo]
    WHERE [DateCreated] < @EarliestDate

    IF @@ROWCOUNT > 0
        GOTO delete_more

But then I thought that perhaps it might be faster if I change @EarliestDate to

DECLARE @EarliestDate DATETIME = @Now - @MaxAgeInDays

Which one would be faster or more clear to both the database, and a dba?

The difference, if any, in performance should be so small that it wouldn't be measurable. The calculation is done either way just once. Deleting thousands of rows will surely need far more time to execute than a single calculation.

Having said that, I'd choose the version with the DATEADD() as adding a date with an integer doesn't look nice or elegant. OK, it may look a bit like a cool "hack" but it's not best practice and is relying on the internal representation of date/datetime types.

See Aaron Bertrand's blog: Bad Habits to Kick : Using shorthand with date/time operations, where he explains how this kind of code works ok with DATE or DATETIME but will break your queries when someone changes the type of the column/variable to DATETIME2.


Other way to write query I'm new to sql language and I'm learning about subqueries..
I have one question is there other way how to write that query?

SELECT Employees.FirstName,
       Employees.LastName
FROM   Employees
WHERE  Employees.HireDate < (SELECT MIN(Employees.HireDate)
                             FROM   Employees
                             WHERE  Employees.City = 'London');

on northwind database schema

Thank you,
I really appreaciate your answer

I won't post the final answer to your homework exercise but you should become familiar with the SELECT TOP(1) ... ORDER BY construct. For example, the query below could be used to get the birth date of the oldest employee.

SELECT TOP(1) BirthDate
FROM Employees
WHERE BirthDate IS NOT NULL
ORDER BY BirthDate


Why is "select *" faster than "select top 500 *" in SQL Server? I have a view, complicated_view -- there are a few joins and where clauses. Now,

select * from complicated_view (9000 records)

is faster, much faster, than

select top 500 * from complicated_view

We're talking 19 seconds vs. 5+ minutes.

The first query returns all 9000 records. How is only picking up the top 500 ridiculously longer?

Obviously, I'm going to look at the execution plans here ---- but once I figure out why SQL Server is running the "top 500" in suboptimal fashion, how do I actually tell it to run the plan the quick way, like taking the full table?

Of course, I may have to rewrite the view entirely --- but quite odd.

Basically, I'm connecting this data table to a 3rd party software that pre-checks tables with a default select top 500 * query that cannot be modified. So other than dumping this view into an actual table (quite sloppy) - I can't get around their "top 500" addendum either.

This is SQL Server 2012.

EDIT: Disagree on the duplicate flag. The other question, the top was FASTER than all. This would be the EXPECTED behavior, returning less rows. My case is the opposite. Also, my understanding is that Top 100 is a different algorithm than Top 100+. I don't even think the duplicate question has the correct answer. Which is, the TOP X query will SORT potentially massive tables very early on, not AFTER they are aggregated/ filtered/ etc. The why is a mystery, but the how is plainly there.

Adding a TOP clause to a query introduces a row goal to the query. The query optimizer will attempt to use the fact that it doesn't need to return all rows to create a more efficient query plan. The row goal can cause the cost of some operators to be scaled down. The row goal optimization can work against the favor of the query tuner due to model limitations or incomplete information in statistics objects. Below I have a demo against a simple view for which adding TOP 500 degrades performance.

First insert only odd integers into a table. Note that I'm gathering statistics in full at the end.

DROP TABLE IF EXISTS dbo.ODD;

CREATE TABLE dbo.ODD (
    ID BIGINT NOT NULL,
    FLUFF VARCHAR(10)
);

INSERT INTO dbo.ODD WITH (TABLOCK)
SELECT TOP (100000)
  -1 + 2 * ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
, REPLICATE('FLUFF', 2)
FROM master..spt_values t1
CROSS JOIN master..spt_values t2;

CREATE STATISTICS S ON dbo.ODD (ID) WITH FULLSCAN;

Then insert only even integers into a different table. I'm doing some things with repeated values and row size to make the demo work. I still update statistics in full at the end.

DROP TABLE IF EXISTS dbo.EVEN;

CREATE TABLE dbo.EVEN (
    ID BIGINT NOT NULL,
    FLUFF VARCHAR(3500)
);

INSERT INTO dbo.EVEN WITH (TABLOCK)
SELECT TOP (100000)
  1000 * FLOOR ( ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) / 500)
, REPLICATE('FLUFF', 700)
FROM master..spt_values t1
CROSS JOIN master..spt_values t2

CREATE STATISTICS S ON dbo.EVEN (ID) WITH FULLSCAN;

Here's the view definition:

CREATE OR ALTER VIEW dbo.TRICKY_VIEW AS
SELECT o.ID
FROM dbo.ODD o
WHERE NOT EXISTS (
    SELECT 1
    FROM dbo.EVEN e WHERE o.ID = e.ID
);

Consider the following query:

SELECT TOP 500 *
FROM dbo.TRICKY_VIEW
OPTION (MAXDOP 1);

Here's what the query plan looks like:

A costing limitation causes the full scan of the EVEN table on the inner side of the nested loop join to have a low relative cost. Based on how I constructed the data we know that the optimizer will need to scan 500 * 100000 = 50 million rows from the EVEN table in order to return the first 500 rows to the client. This is indeed what happens, and the query takes about 16 seconds to execute on my machine:

Removing the TOP clause from the query gives a different and more efficient plan:

This query executes in less than half a second on my machine. Only 100000 rows are read from the EVEN table.

For SQL Server 2016 and later versions you can work around this issue without changing the view's definition by adding OPTION (USE HINT('DISABLE_OPTIMIZER_ROWGOAL')) to the query. That hint disables the row goal optimization at the query level. For SQL Server 2012 you can use trace flag 4138 at the query level via OPTION (QUERYTRACEON 4138), but that requires SA.

I can't say anything about your query in particular without seeing the query plans, but hopefully this example illustrates the general point.


OutOfMemory Exception duing mysqldump I am trying to add a slave MySQL server to an existing server. I ran the following mysqldump on the master to prepare the data for the slave initially.

mysqldump.exe -u ... -p... --single-transaction --flush-logs --skip-lock-tables --master-data=2 > all-dbs.sql

But after a while, it gave the following error;

Program 'mysqldump.exe' failed to run: Exception of type 'System.OutOfMemoryException' was thrown.At line:1 char:1
+ .\mysqldump.exe -u ... -p... --skip-lock-tables --single-tran ...
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~.
At line:1 char:1
+ .\mysqldump.exe -u ... -p... --skip-lock-tables --single-tran ...
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : ResourceUnavailable: (:) [], ApplicationFailedException
    + FullyQualifiedErrorId : NativeCommandFailed

The master MySQL server has 3.5GB memory, and the data is about 5GB.
It is running MySQL 5.7.
Any help would be greatly appreciated.  Thanks.

Just like  Michael - sqlbot suggested, --quick would help. Official MySQL Doc states:

  --quick, -q

  This option is useful for dumping large tables. It forces mysqldump to
  retrieve rows for a table from the server a row at a time rather than
  retrieving the entire row set and buffering it in memory before
  writing it out.

http://dev.mysql.com/doc/refman/5.7/en/mysqldump.html#option_mysqldump_quick

Regarding the PowerShell issue, there's a note on MySQL Doc:

  Note:

  A dump made using PowerShell on Windows with output redirection
  creates a file that has UTF-16 encoding:

  shell> mysqldump [options] > dump.sql

  However, UTF-16 is not permitted
  as a connection character set (see Section 11.1.4, “Connection
  Character Sets and Collations”), so the dump file will not load
  correctly. To work around this issue, use the --result-file option,
  which creates the output in ASCII format:

shell> mysqldump [options] --result-file=dump.sql

http://dev.mysql.com/doc/refman/5.7/en/mysqldump.html


Facing error while working with Aggregate function in MDX (MultiDimensional-Expresson) Hi everyone hope you're doing well
I'm using Aggregate function to aggregate the value of my measure
for two specific years as you can see below :
select aggregate ({[DimTime].[Year].&[1396] ,[DimTime].[Year].&[1397] },[Measures].[IndentCount]) on 0 from [Procurement]

according to the fact that aggregate function accepts two parameters :
Aggregate (Set_expression , Numeric_Expression)

What is wrong with my MDX query?
I get this error :
The Axis0 function expects a tuple set expression for the  argument. A string or numeric expression was used.

Thanks in advance

Look again at the details for the Aggregate() function. You got the parameters correct, but you need to look at what the function will return. It returns a numeric value.

So your MDX evaluates as SELECT some-numeric-value ON 0 FROM [Cube] which is not valid.

You need SELECT some-sort-of-set ON 0 FROM [Cube]

Try googling for examples of using this function (or see link above, which includes examples) to see how it can be used.


UPDATE and CASE problem It is my first question in this forum.
I wrote this code, but it is incorrect and I can't find a problem.
I have 2 tables: sciana (gid,id,geom) and sciana1(gid,id,geom).
If table sciana is empty I want to sciana.id = sciana.gid, but if sciana is not null I want to find max(sciana.id) + sciana1.gid

UPDATE sciana1
SET id =
CASE
WHEN sciana.gid IS NULL THEN
    id = sciana1.gid
WHEN sciana.gid IS NOT NULL THEN
    (SELECT max(sciana.id)+sciana1.gid
    FROM sciana )
END;

First example:

`sciana` IS EMPTY, so after the update `sciana1` looks like

         1,1,geom1
         2,2,geom2
         ...

Second example:

sciana IS NOT EMPTY

         1,1,geom1,
         ...
         9,9,geom9

so after the update, sciana1 contains

         1,10,geom10
         2,11,geom11
         ...

I think you want to do something like

UPDATE sciana1
   SET id = gid + COALESCE((SELECT max(id) FROM sciana), 0);

COALESCE() will return you the first non-NULL parameter.  Then max(id) is NULL for an empty table, which means the COALESCE expression returns 0 when the table is empty, and the actual maximum when it is not.

You can further improve this by checking if the value will be different after the update, thus sparing unnecessarily bloating the table:

UPDATE sciana1
   SET id = gid + COALESCE((SELECT max(id) FROM sciana), 0)
 WHERE id IS DISTINCT FROM gid + COALESCE((SELECT max(id) FROM sciana), 0);

IS DISTINCT FROM is an operator that behaves similarly to <>, but also considers NULLs.  With a plain <> you might not get anything updated, as the comparison itself results in NULL (thus not matching any rows) if sciana1.id is empty.

One more important thing is that if sciana is big, an index on id can help a lot in finding its maximum.

And a last note: in the second UPDATE query, the SELECT max()... part is executed twice.  This can be prevented by moving it out to a CTE or a subquery like

UPDATE sciana1
   SET id = gid + COALESCE(m, 0)
  FROM (SELECT max(id) FROM sciana) AS t(m)
 WHERE id IS DISTINCT FROM gid + COALESCE(m, 0);

One should check which one results in a more efficient execution plan.  In my experiments, the latter produces a nested join, which with a higher number of rows in both tables gets slower than the first version, while being faster with low row numbers.


Logging the Execution times of dynamically constructed mysql stored procedures I have a MySQL Server.

The MySQL server contains a huge network data collection.
The client API's will connect to the MySQL server and execute some dynamically created stored procedures and delete it on the fly after successful execution.
There is no tracking mechanism currently available.
I wont be able to modify the stored procedures, as it is dynamically created by the client API's

Is there any mechanism to track the execution of such dynamically created stored procedures ?

You could capture the mysqld's uptime or the DB Server's time at the start and end of your code.

Perhaps something like this

SUGGESTION #1

SELECT variable_value INTO @t1
FROM INFORMATION_SCHEMA.GLOBAL_STATUS
WHERE variable_name='uptime';
<Your Stored Procedure Code>
SELECT variable_value INTO @t2
FROM INFORMATION_SCHEMA.GLOBAL_STATUS
WHERE variable_name='uptime';
SET @RunningTimeSec = @t2 - @t1;
SET @RunningTime = SEC_TO_TIME(@RunningTimeSec);
INSERT INTO sometable ... VALUES (...,@RunningTimeSec,...);

SUGGESTION #2

SET @t1 = UNIX_TIMESTAMP(NOW());
<Your Stored Procedure Code>
SET @t2 = UNIX_TIMESTAMP(NOW());
SET @RunningTimeSec = @t2 - @t1;
SET @RunningTime = SEC_TO_TIME(@RunningTimeSec);
INSERT INTO sometable ... VALUES (...,@RunningTimeSec,...);

GIVE IT A TRY !!!


Oracle extents - How can I have so few with this large size? Note on duplication:

This is not a duplicate of "Oracle extents - How can I have so many with this small size?"  This is a separate question I entered which happens to be in the same domain.

This is a duplicate of "Oracle extents - how is it possible to get so many? [closed]" - this is a question that was closed because someone thought it was a duplicate of the above question, apparently without reading either question, and despite the fact that neither question had an answer. I see why this Stackexchange is not exactly flourishing.

Question:

This is more curiosity about how Oracle Database works than anything I need to change (yes I know multiple extents are fine, I read Tom Kyte too).

I have an index with the following characteristics:

Size:        376MB
Initial Ext: 118MB
Next Ext:    1MB
Num Extents: 12

In case it's not apparent, my question is, how can I have something that size, with those extents sizes, and only 12 extents? 376 > 118 + (1 * 11)

FYI: to Justin Cave, yes, it's locally-managed.

Thank you.

You may believe that this is a different question but my answer is going to be essentially identical assuming the additional information from the prior question is the same-- you're using automatic extent allocation and you're not sure how Toad is determining these numbers.

The data you're getting from Toad appears to be incorrect or, at least, misleading.  If you are using a locally managed tablespace with automatic extent allocation, Oracle will determine your initial and next extent sizes automatically.  In 11.2, the first 16 extents are going to be 64k in size (for a total of 1 MB).  The next 63 extents are going to be 1 MB in size.  So if you have 12 extents, that implies that the table occupies 768 kb of space on disk.  An initial extent of 118 MB makes no sense and a size of 376 MB makes no sense.

What does

SELECT tablespace_name,
       extent_id,
       bytes/1024 kb
  FROM user_extents
 WHERE segment_name = <<name of index>>

show you?  If you have 12 extents, you should see that extents 0-11 are 64 kb in size.

SELECT SUM(bytes)/1024/1024 size_in_mb
  FROM user_extents
 WHERE segment_name = <<name of index>>

will show you the total size of the table's extents on disk.


GROUPING SETS returning unexpected results with calculated expression Here we have two similar queries using grouping sets
where the SELECT clause contains some expressions calculated in aggregation:

SELECT RN10, RN10 / 10, COUNT(*) FROM
(
       SELECT RN, RN/10 AS RN10, RN/100 AS RN100 FROM
       (
               SELECT RN = -1 + ROW_NUMBER() OVER (ORDER BY 1/0)
               FROM master..spt_values
       ) A
) B
GROUP BY GROUPING SETS ((RN10), (RN10 / 10), ())
ORDER BY 1, 2

it's plan is here: first query plan

and

SELECT RN10, SUBSTRING(RN,3,99), COUNT(*) FROM
(
       SELECT RN, SUBSTRING(RN,2,99) AS RN10 FROM
       (
               SELECT RN = CAST(-1 + ROW_NUMBER() OVER (ORDER BY 1/0) AS VARCHAR(99))
               FROM master..spt_values
       ) A
) B
GROUP BY GROUPING SETS ((RN10), (SUBSTRING(RN,3,99)), ())
ORDER BY 1, 2

the corresponding plan is here: second query plan

Both the queries first calculate some expression for aggregation, RN10 / 10 in the first case and SUBSTRING(RN,3,99) in the second, then the same expression is used in the SELECT clause but as the first plan shows it's re-calculated in the first query and it's not in the second.

As the result we have NULLs in the first result set that is quite unexpectedly:

Can someone explain why the first query makes the calculation 2 times (one in aggregation and one more time in the final select) while the second makes it one time only?

I'm going to use a simpler example where it is clear to see what the expected results are.

CREATE TABLE Queen
(
   FirstName        VARCHAR(7),
   Surname          VARCHAR(7)
);

INSERT INTO Queen
    (FirstName, Surname)
VALUES
    ('Brian',   'May'),
    ('Freddie', 'Mercury'),
    ('John',    'Deacon'),
    ('Roger',   'Taylor')
;

Query 1

SELECT Surname,
       NULL AS SurnameInitial,
       COUNT(*) AS Count
FROM   Queen
GROUP  BY Surname
UNION ALL
SELECT NULL AS Surname,
       LEFT(Surname,1) AS SurnameInitial,
       COUNT(*) AS Count
FROM   Queen
GROUP  BY LEFT(Surname,1)

Query 1 Results

+---------+----------------+-------+
| Surname | SurnameInitial | Count |
+---------+----------------+-------+
| Deacon  | NULL           |     1 |
| May     | NULL           |     1 |
| Mercury | NULL           |     1 |
| Taylor  | NULL           |     1 |
| NULL    | D              |     1 |
| NULL    | M              |     2 |
| NULL    | T              |     1 |
+---------+----------------+-------+

Query 2

SELECT Surname,
       LEFT(Surname,1) AS SurnameInitial,
       COUNT(*) AS Count
FROM   Queen
GROUP  BY GROUPING SETS ( ( Surname ), (LEFT(Surname,1)) )
ORDER BY SurnameInitial, Surname

Query 2 Results

Despite the ORDER BY SurnameInitial and the fact that NULL sorts first in SQL Server the rows with SurnameInitial as NULL are ordered last.

+---------+----------------+-------+
| Surname | SurnameInitial | Count |
+---------+----------------+-------+
| Deacon  | D              |     1 |
| May     | M              |     1 |
| Mercury | M              |     1 |
| Taylor  | T              |     1 |
| NULL    | NULL           |     1 |
| NULL    | NULL           |     2 |
| NULL    | NULL           |     1 |
+---------+----------------+-------+

Query 1 and 2 should return the same results. The problem is that SQL Server decides to treat it like the following SQL

WITH GrpSets AS
(
SELECT Surname,
       COUNT(*) AS Count
FROM   Queen
GROUP  BY Surname
UNION ALL
SELECT NULL AS Surname,
       COUNT(*) AS Count
FROM   Queen
GROUP  BY LEFT(Surname,1)
)
SELECT Surname,
       LEFT(Surname,1) AS SurnameInitial,
       Count
FROM GrpSets

This just looks like a bug to me (trace flag 8605 shows that the damage is already done in the initial query tree representation). BUG REPORT.

Query 3

SELECT Surname,
       LEFT(FirstName,1) AS FirstNameInitial,
       COUNT(*) AS Count
FROM   Queen
GROUP  BY GROUPING SETS ( ( Surname ), (LEFT(FirstName,1)) )

Query 3 Results

+---------+------------------+-------+
| Surname | FirstNameInitial | Count |
+---------+------------------+-------+
| NULL    | B                |     1 |
| NULL    | F                |     1 |
| NULL    | J                |     1 |
| NULL    | R                |     1 |
| Deacon  | NULL             |     1 |
| May     | NULL             |     1 |
| Mercury | NULL             |     1 |
| Taylor  | NULL             |     1 |
+---------+------------------+-------+

Query3 does not meet the problematic pattern of grouping on a column and an expression referencing that column. It wouldn't even be possible for the same issue to occur here anyway because the grouping sets part is equivalent to

SELECT Surname,
       NULL AS FirstNameInitial,
       COUNT(*) AS Count
FROM   Queen
GROUP  BY Surname
UNION ALL
SELECT NULL AS Surname,
       LEFT(FirstName,1) AS FirstNameInitial,
       COUNT(*) AS Count
FROM   Queen
GROUP  BY LEFT(FirstName,1)

This does not pass out the entire FirstName column upstream (or even have a guaranteed unique  FirstName column that could be passed out) so it isn't possible for the LEFT(FirstName,1) expression to be calculated on top of that.

For the same reason you don't see the issue with (RN10), (SUBSTRING(RN,3,99)).

@i-one reasons in the comments that it is likely

  a bug in normalization (algebrization). It has logic that finds a
  match for non-aggregated columns and expressions in the SELECT list
  within members of GROUP BY. The same logic seemingly allows us to
  write for example

SELECT Surname, LEFT(Surname, 1), COUNT(*)
FROM   Queen
GROUP BY Surname

without having to explicitly add the calculated expression as below

GROUP BY Surname, LEFT(Surname, 1)

Or another example would be

SELECT Surname,
       LEFT(Surname,1) AS SurnameInitial,
       LEFT(Surname,2) AS SurnamePrefix,
       COUNT(*) AS Count
FROM   Queen
GROUP  BY GROUPING SETS ( ( Surname ), (LEFT(Surname,1)) )

In this case the LEFT(Surname,2) is allowed and the only way of computing it would be to do it in the manner that is problematic for the LEFT(Surname,1) case.


MySQL InnoDB add new instance to existing cluster Server version: 5.7.23-log MySQL Community Server (GPL)

I'm running an InnoDB cluster and trying to add a new node, the cluster is a couple of years old and binlogs are being cleared after 2 weeks.

Adding a new node to a new cluster is straight forward enough because the master has all the binlogs but I can't find anywhere pointing me to instructions on how to add a new node to an existing cluster.

It's in production and I don't want to 'test and see' in case I break something so I'm looking for a definitive set of instructions.

Can anyone assist please?

The old way of adding a Slave might be best:

Stop mysqld on an existing Slave (Replica)
Copy the disk (or at least all of MySQL's stuff)
Change the server_id
See if something in the Master contains a list of servers in the topology; if so, augment it
Start mysqld on both replicas.


MySQL: cannot run change master to inside mysql I am trying to create a stored procedure that can run change master to command.

This is what I was able to come up with.

DELIMITER //
CREATE PROCEDURE changeMasterTo(IN ip char(20),IN port int,IN user
char(30),IN password char(30))
LANGUAGE SQL
COMMENT 'changes master to'
ThisStoredProcedure:BEGIN
CHANGE MASTER TO MASTER_HOST=ip,
MASTER_PORT=port,
MASTER_USER=user,
MASTER_PASSWORD=password,
MASTER_AUTO_POSITION=1,
MASTER_SSL=1;
END;
//

But it fails with the following error.

  ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near

  'ip,
  MASTER_PORT=port,
  MASTER_USER=user,
  MASTER_PASSWORD=password,
  MASTER_AUTO_P'

  at line 5

I am not sure what the issue is, any help will be appreciated.

The host, user, and password values are supposed to be enclosed in single quotes.

You are also better off executing it as Dynamic SQL

DELIMITER //
DROP PROCEDURE IF EXISTS changeMasterTo //
CREATE PROCEDUREchangeMasterTo(IN ip char(20),IN port int,IN user
char(30),IN password char(30))
LANGUAGE SQL
COMMENT 'changes master to'
ThisStoredProcedure:BEGIN
    SET @sql = 'STOP SLAVE';
    PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

    SET @sql = CONCAT('CHANGE MASTER TO MASTER_HOST=',QUOTE(ip));
    SET @sql = CONCAT(@sql,',MASTER_PORT=',port);
    SET @sql = CONCAT(@sql,',MASTER_USER=',QUOTE(user));
    SET @sql = CONCAT(@sql,',MASTER_PASSWORD=',QUOTE(password));
    SET @sql = CONCAT(@sql,',MASTER_AUTO_POSITION=1,MASTER_SSL=1');
    PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;
END;
//
DELIMITER ;


Is database partitioning only created to get a better performance? and why should one partition a table, and a database? I was asked if SQL Server can handle huge databases ( for them a huge database is 100GB). I know it can. I worked with 4tB databases already. But I would like to ask, thinking about really huge databases ( I don't know. maybe 30TB ).

In a busy environment, a 30tb database would be a problem if not indexed or partitioned correctly. An example is, if a table is not partitioned, when those 500 concurrents queries run, the table would be really slow to give us results, so we can partition this table, let's say, by months or years.

Is Database partition and Table partition the same? Not literal the same, but, is this for the same result? Only performance?

In the company where I work, we barely have a query. We only "hold" data. theres less then 3 queries running each time. If I have a huge database, will I have performance issues ( With so little interaction on it?)

I don't know if I could explain what I would like to.

I am not sure if I would be able to put this in a comment, hence making it an answer:

Partitioning may not serve the mere goal of performance, because querying the partitioned tables and data comes more into picture when we talk performance. Therefore one needs to have a better understanding of writing efficient queries on partitioned tables.

Partitioning is good for very large databases, for maintaining that big amount of data within the database efficiently so that you can handle its growth properly. What I get from partitioning is saving some space on the data drives, which might be filling with unwanted data preserved, depending upon RTO or RPO. Good for saving some space for sure.

I don't think database partitioning and tables are the same. I have never implemented database partitioning, but yes you can use it for large tables. Also check on data and/or page compression when using partitioning.

With big databases, like 30 TB, you may consider other performance checks and good practices apart from partitioning as well, like infra build, etc.

This might help: https://www.brentozar.com/guide-to-evaluating-table-partitioning/


Materialized View for SQL Server I want to know if there is some thing similar to materialized view for Oracle in SQL Server .

Any ideas ?

A view can be materialised by creating an index on it. There are several preconditions which must be met. These may be tricky to impose on large, established applications with layered views.


Last Known Successful DBCC CHECKDB I understand that looking at DBCC PAGE is supposed to show me the last known good DBCC CHECKDB but I am not seeing this work correctly.

I have a database that I know is corrupted. If I run DBCC CHECKDB the errors are displayed as expected (see below).

CHECKDB found 0 allocation errors and 1 consistency errors in table 'Blah'
   (object ID 251147940).
Msg 2508, Level 16, State 3, Line 1

When I run the below to evaluate dbi_dbccLastKnownGood, I get the time that the CHECKDB was just run down to the second.

DBCC PAGE ('Blah', 1, 9, 3) WITH TABLERESULTS;

I've read Paul Randal's information, and from that, it looks like I'm doing/reading this correctly. Has anyone seen this before and if so, any thoughts on working around it?

Here is a complete sample of the error. I am getting 12 of these for different tables.

    Msg 2508, Level 16, State 3, Line 1
The In-row data RSVD page count for object "Mailings", index ID 0, partition ID 16459231395840, alloc unit ID 16459231395840 (type In-row data) is incorrect. Run DBCC UPDATEUSAGE.
CHECKDB found 0 allocation errors and 1 consistency errors in table Mailings' (object ID 251147940).

Community wiki answer:
From CHECKDB From Every Angle: When did DBCC CHECKDB last run successfully? by Paul Randal:

One of the new features we put into SQL Server 2005 was storing the last time that DBCC CHECKDB completed successfully (called the last-known good time). It’s the time that the DBCC CHECKDB started, not completed.
What does successfully mean? This is the catch – if DBCC CHECKDB runs to completion then it considers that a successful run – EVEN if it found some corruptions. However, the last-known good time is ONLY updated if there were NO corruptions found. Slightly confusing I know.

Does seem somewhat contradictory, but I agree it sounds like this is not expected behaviour for that field to be updated if errors were reported.
On the other hand, your examples are quite minor issues, easily fixed by running DBCC UPDATEUSAGE so maybe SQL Server regards that as a successful run.


RIGHT() function in postgresql 9.1 not behaving as anticipated? The following is with regard to PostgresQL 9.1 :

I am using the right() and left() string functions to fix a few instances where data was input into my database being “off by one” .. so I’m doing something along the lines of right(field1,1)||left(field2,-1) to get correct values and such.  In the process, I encountered an unexpected result on a char(2) column.

If I do SELECT RIGHT('A ',1) it gives me the expected ' ' returned.

However, if I do SELECT RIGHT('A '::CHAR(2),1) it gives me 'A'.

Why is it doing this?  Is it a bug?  I tried SELECT RIGHT('A  '::CHAR(3),1) and it also gives 'A' as a result.

The CHAR data type is awful. Avoid it if at all possible, its padding rules are insane. PostgreSQL wouldn't support it at all if the standard didn't require support for it.

It's not clear whether this is intended behaviour or a bug. Usually, leading white-space gets stripped from CHAR before processing, and I suspect that for RIGHT, Pg is stripping the whitespace from the right, as if this were RTL text.

I suggest raising this for discussion on pgsql-general. If nothing else, it clearly warrants a documentation note.


Issues with mysqldump after upgrade 5.5 -> 5.6 A few months ago I updated a MySQL setup from 5.5 to 5.6. Since then I've been having problems with a script that I use to dump the various databases so that I can back them up.

The script is a short piece of perl that gets a list of all databases and then calls mysqldump for each as follows:

mysqldump -udb_account -pdb_pw -hserver.com --single-transaction --flush-logs
   --routines --triggers --quick $fn 2> $fn.err | gzip > $fn.mysql.gz

Issue: many of these databases have hundreds of tables (ever increasing). For these larger dbs the mysqldump command often exits after only a single table. If I run the command from a terminal session it executes correctly. (typically it runs as a cron job 1x/wk)

The .err file contains no messages. Neither does the server.err file in the MySQL root dir.

Note: this script had been running fine on MySQL 5.5 for several years. This problem started happening when I upgraded to 5.6.

Also: the --flush-logs portion isn't working. The mysql_bin folder has never been emptied since this system was brought online.

One variable that I haven't controlled for yet: when run as a CRON job the script forks 3 processes at a time. When I test the command in a term session I'm only doing one at a time.

System in question:

CentOS 6.4
x64
64Gb RAM

The problem here is just a misunderstanding
Using --flush-logs with mysqldump simply closes and opens all files handles against the following:

Error Log
Engine Logs (Redo Logs Flushed to disk)
Relay Log (If Replication is setup)
General Log (if Enabled)
Slow Log (if Enabled)
Binary Logs (if Enabled)
See MySQL Documentation under Bulletpoint #3 ([log_type] LOGS)

In the case of binary logs, the current binary log is closed and a new binary log is open. In essence, it is the only log file that is numerically rotated For example, suppose you run SHOW MASTER STATUS; and you see the binary log file names mysql-bin.000027. Running FLUSH LOGS; or FLUSH BINARY LOGS; would close mysql-bin.000027 (which stops recording transactions to that file) and open mysql-bin.000028 (which now records incoming transactions).
However, rotation does not imply automatic deletion.
There are two ways to clean up the binary logs
DELETE ALL BINARY LOGS
If the DB Server is not used as MySQL Replication Master, then run
BINARY LOGGING IS ENABLED
mysql> RESET MASTER;

That will erase all binary logs and start with the first one (like mysql-bin.000001)
BINARY LOGGING IS NOT ENABLED
Simply go the the OS and run the delete command (rm for Linux,del for Windows)
PURGE BINARY LOGS
If you want to keep binary logs from the last 48 hours, you run
mysql> PURGE BINARY LOGS BEFORE NOW() - INTERVAL 48 HOUR;

mysqld will erase all binary logs older than 48 hours.
AUTOMATIC PURGING
Simply set the following in my.cnf (or my.ini)
[mysqld]
expire_logs_days=2

Then, either restart mysqld or run the following as root
mysql> SET GLOBAL expire_logs_days = 2;

That way, every manual or automatic flush of binary logs will delete logs older than 2 days.
Once expire_logs_days is set, mysqldump will cleanup old logs every time it does  --flush-logs.


Out of operation records in local data manager (increase MaxNoOfLocalOperations) from NDBCLUSTER MaxNoOfConcurrentOperations=150000
MaxNoOfConcurrentTransactions=150000
MaxNoOfLocalOperations=150000

I have above parameters in config.ini, when i try to delete rows in a table i get below error

mysql> delete from radacct where acctinputoctets=0 and acctoutputoctets=0;
ERROR 1297 (HY000): Got temporary error 1217 'Out of operation records in local data manager (increase MaxNoOfLocalOperations)' from NDBCLUSTER.

mysql> explain delete from radacct where acctinputoctets=0 and acctoutputoctets=0;
+----+-------------+---------+------+---------------+------+---------+------+---------+-------------+
| id | select_type | table   | type | possible_keys | key  | key_len | ref  | rows    | Extra       |
+----+-------------+---------+------+---------------+------+---------+------+---------+-------------+
|  1 | SIMPLE      | radacct | ALL  | NULL          | NULL | NULL    | NULL | 1209607 | Using where |
+----+-------------+---------+------+---------------+------+---------+------+---------+-------------+

How to determine the optimum value for maxnooflocaloperations ?

vi config.ini, /var/lib/mysql-cluster/XX/config.ini or /var/lib/mysql-cluster/config.ini
※XX : id of ndb_mgmd(MGM) node
add MaxNoOfConcurrentOperations=2000000
restart ndb cluster


Determine minimal permissions required for a given SQL script or session For security reasons I need to determine minimal permissions required for a given script or session without having to manually audit activity then work it out by hand.

I'm assuming this can be done by exhaustively tracing all the statements executed, which objects they refer to and which permissions are required to run that statement, but I don't really have the skills or time to build that.

Is there an out of the box solution to this?

I would like to be able to do this for SQL Server 2016 and SQL Azure.

I am with Stacy on this one, abstracting the user to make sure they cannot do anything directly and controlling the view/SP code, would work in pretty much all cases. If you run into more complex issues because of a dependancy on elevated permissions (e.g. view serverstate) you may want to look into custom server/application/database roles depending on your need. For checking the permissions you will need for a given task, you could have a look at the permission poster for SQL Server 2016 (https://github.com/microsoft/sql-server-samples/tree/master/samples/features/security/permissions-posters). Azure uses the same permissions, so if it works on-prem it will also work in Azure (according to the applies to statement), but check it to be sure. Azure does have a few less then well documented features here and there. :-) Good luck!

Feel free to mark the awnser or upvote. :-) If you need more info, just let me know.


What is the best practice when an index with key/include columns are inversed for a second index on the same table I have an index on a table of the form:

NONCLUSTERED INDEX [IDX1] ON [TABLE]
(
    [COL_ID_A] ASC
)
INCLUDE
(
    [COL_ID_B]
)

For a different query on the same table MSSMS proposes the following index where the key and include columns are reversed:

NONCLUSTERED INDEX [IDX2] ON [TABLE]
(
    [COL_ID_B] ASC
)
INCLUDE
(
    [COL_ID_A]
)

What is the best practice (and why)?:

one index with keys and indexes combined or
two separate indexes

You may read more about non-clustered index here from Brent Ozar and Team. Thumb rule for column in the index should be used in where clause and include is the column which is in the select statement.

Since, you have not posted query here, you may understand with below examples:

/*Query1
********/
select [COL_ID_B]
from [TABLE]
where [COL_ID_A] = some value

Above query Query1 will use index IDX1, whereas Query2 will use IDX2.

/*Query2
*********/
select [COL_ID_A]
from [TABLE]
where [COL_ID_B] = some value

Now, there is difference between index column and include column, you can read more about this on article written by Mr. Ahmad Yaseen. There is also concept of hidden column in case you have clustered index on this table, clustered column will be part of all non-clustered index, you may refer this article.

Whether one index should contain multiple column or multiple index should be created with one column, it depends on application and query pattern. Thumb rule is to have five non-clustered index on 5 columns however this varies from application to application.

First column in index is extremely important, if your query selects col1 and looks mostly for colA followed by colB, colC and colD respectively then, you should go for a non-clustered index with below definition:

CREATE NONCLUSTERED INDEX [IDX3] ON [TABLE]
(
    colA,
    colB,
    colC,
    colD
)
INCLUDE
(
    col1
)

however if your queries have where clause on all columns and heavily uses all of them i.e. colA, colB, colC and colD then, five separate non-clustered index on each of them would help. Again column in select could be chosen as include.

There are tonnes of article on each of these topic by many experts. you may refer below for more details:

https://www.brentozar.com/archive/2018/11/tales-from-overindexing-too-many-one-column-indexes/

https://www.brentozar.com/archive/2015/04/index-included-columns-multi-column-statistics/

Similar question was asked and you may find answers here

Please let us know if this helped.


SQL Server 2008 - How to check FK integrity? I'd like to pull a partial dataset from production for test environments - e.g. Copy the production database with only the first 100 rows of each table or 5% of the data - which ever comes first. Simple enough. For each table, just... SELECT TOP 100 * INTO DESTINATION FROM SOURCE"

The problem is maintaining data integrity for FK. Is there a native SQL Server SP/FX to verify the integrity of each row's FK after the data has been copied over?

If not, once the production schema is copied to sandbox environments, I was planning to implement the following logic:

PSUEDO:

For each source table{
 While (Destination Tbl =<100 rows/5% of Source Tbl COUNT(*)) {
   If FK exists on source tbl{
     For each FK, query referenced FK tbl and insert results into FK's corresponding destination tbl
   }
  Insert row from source tbl to destination tbl
 }
}

I don't want to recreate the wheel or make it harder than it needs to be. If you see any holes in my logic, please let me know. Thanks in advance.

I think you are over complicating your requirement.

Your best option is to

Create a backup of PROD on a test server
drop all the FK's
migrate the data you want to destination server using SSIS or T-SQL or BCP (which ever one you prefer).
create FK's back.

In SQL Server you don't have a tool to do it out of the box. You will end up writing more complex tsql code which I doubt will achieve what you are trying to do + you have to take care of Identity columns as well.

Below script would loop over all the tables and select them one-by-one by taking into account referential integrity constraints (i.e. foreign keys).

Note: Below script is borrowed and modified from here written by Jamie Thomson.

    -- make sure to use SQLCMD mode in SSMS and change the database Name.
    :setvar db Adventureworks2008

    DECLARE @tables TABLE (
        schemaName sysname
    ,   tableName sysname
    ,   level int
    );

    with fk_tables as (
        select  s1.name as from_schema
        ,       o1.Name as from_table
        ,       s2.name as to_schema
        ,       o2.Name as to_table
        from    $(db).sys.foreign_keys fk
        inner   join $(db).sys.objects o1
        on      fk.parent_object_id = o1.object_id
        inner   join $(db).sys.schemas s1
        on      o1.schema_id = s1.schema_id
        inner   join $(db).sys.objects o2
        on      fk.referenced_object_id = o2.object_id
        inner   join $(db).sys.schemas s2
        on      o2.schema_id = s2.schema_id
        --For the purposes of finding dependency hierarchy
        --we're not worried about self-referencing tables
        where   not (   s1.name = s2.name
                    and o1.name = o2.name)
    )

    ,ordered_tables AS (
        SELECT  s.name as schemaName, t.name as tableName, 0 AS Level
        FROM    (   select  *
                    from    $(db).sys.tables
                    where   name <> 'sysdiagrams') t
        INNER   JOIN $(db).sys.schemas s
        on      t.schema_id = s.schema_id
        LEFT    OUTER JOIN fk_tables fk
        ON      s.name = fk.from_schema
        AND     t.name = fk.from_table
        WHERE   fk.from_schema IS NULL
        UNION   ALL
        SELECT  fk.from_schema, fk.from_table, ot.Level + 1
        FROM    fk_tables fk
        INNER   JOIN ordered_tables ot
        ON      fk.to_schema = ot.schemaName
        AND     fk.to_table = ot.tableName
    )
    insert  @tables
    select  distinct ot.schemaName,ot.tableName,ot.Level
    from    ordered_tables ot
    inner   join (
            select  schemaName,tableName,MAX(Level) maxLevel
            from    ordered_tables
            group   by schemaName,tableName
    ) mx
    on ot.schemaName = mx.schemaName
    and ot.tableName = mx.tableName
    and mx.maxLevel = ot.Level
    ORDER   BY [Level] DESC;

    DECLARE tables_cursor CURSOR FORWARD_ONLY
    FOR
    SELECT  schemaName, tableName
    FROM    @tables
    ORDER   BY [Level] DESC;

    DECLARE @schemaName         sysname
    ,       @tableName          sysname
    ,       @schemaTableName    NVARCHAR(MAX);

    DECLARE @vSQL nvarchar(max);
    OPEN    tables_cursor;
    FETCH   NEXT FROM tables_cursor INTO @schemaName, @tableName

    BEGIN TRAN
    BEGIN TRY
        WHILE   @@FETCH_STATUS = 0
        BEGIN
                SET @schemaTableName = '[' + @schemaName + '].[' + @tableName + ']';
                SET @vSQL = 'select top(100) * FROM $(db).' + @schemaTableName;
                exec (@vSQL);
                PRINT 'Selected ' + CAST(@@ROWCOUNT AS VARCHAR) + ' rows from $(db).' + @schemaTableName;
                FETCH NEXT FROM tables_cursor INTO @schemaName, @tableName;
        END
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0
            ROLLBACK TRAN;
        PRINT ERROR_MESSAGE();
    END CATCH

    IF @@TRANCOUNT > 0
        COMMIT TRAN;

    IF CURSOR_STATUS('global','tables_cursor') >= 0
    BEGIN
        CLOSE tables_cursor;
        DEALLOCATE tables_cursor;
    END
    GO