Viable strategies for rebuilding indexes online in SQL Server 2008 R2 Standard edition? We use SQL Server 2008 R2 Standard edition (we can't afford Enterprise edition on all our production database servers) and have to periodically maintain the critical indexes (rebuilding/reorganizing) in our production servers.  The problem is that since we are using Standard edition we don't have access to the rebuild index online option.  If we don't limit access to the database while these critical indexes are rebuilt, we get many timeout errors because the index is essentially disabled during rebuild. I've used scripts such as Ola Hallengren's Index Optimize script, however this doesn't seem to solve my problem.

What I'm thinking to do to work around this problem is instead of rebuilding an index when an index is fragmented, create a separate index copy (i.e. IX_some_name_temp), once that is done, drop the original fragmented index and finally rename the copy index back to the original name.  What I'm hoping is that while the new index is being built Sql Server can use the original (somewhat fragmented index), then after the new temp index is built it can start using that one, then we drop the fragmented index and rename and we're back to the original state for the next time we have to run our scheduled job.

My question is, does this approach make sense?  Will sql server be able to utilize my new temp index copy while I'm dropping the original fragmented index?
Any tips or possibly other strategies are appreciated.

Assuming these are non clustered indexes then the CREATE INDEX topic on BOL discussing offline operations indicates this approach should only partially work in that it will allow read only access to the table but will still block updates. The relevant section is below (emphasis mine)

  Table locks are applied for the duration of the index operation. An
  offline index operation that creates, rebuilds, or drops a clustered
  index, or rebuilds or drops a nonclustered index, acquires a Schema
  modification (Sch-M) lock on the table. This prevents all user access
  to the underlying table for the duration of the operation. An offline
  index operation that creates a nonclustered index acquires a Shared
  (S) lock on the table. This prevents updates to the underlying table
  but allows read operations, such as SELECT statements.


Can multiple fact tables have the same structure? I'm designing the schema of a data base and I have two solutions :
the first is to use one fact table linked to multiple dimensions.
the second is to use multiple fact tables with same structure for each group (I have eight groups ) and all these fact tables are linked to the same eight dimensions.

I think the second solutions sounds good because I can avoid some joins to analyse per group.But I'm not sure about using multiple fact tables with same attributes for each group.
for example :
Fact1:
Id_Ap #|Id_cst#|Id_Ac#|Id_Dt#|Bp
Fact2:
Id_Ap#|Id_cst#|Id_Ac#|Id_Dt#|Bp
.. Fact 8 : the same.

Yes and depending on what you are doing that might be a very good idea. I will use a request table as an example.

Request comes in and goes to a submitted request table. After it gets processed it moves to a pending table. If it gets approved it moves to an approved/completed table. If it gets canceled it gets moved to a canceled table. Having the same table structure allows for the system to easily move data around and for users to easily select data from any of the tables.

While this can also be done with a status column the design of the database and amount of data could make it more desirable to have a separate table for each state.


How do I SELECT from a table with a JOIN with multiple matching values? I have the following simple query that works just fine when there is one keyword to match:

SELECT gc.id, gc.name
FROM gift_card AS gc
JOIN keyword ON gc.id = keyword.gc_id
WHERE keyword = 'mini'
GROUP BY gc.id
ORDER BY id DESC

What I want to do is find the id's that match at least two of the keywords I provide. I thought just adding a simple AND would work but I get blank results.

SELECT gc.id, gc.name
FROM gift_card AS gc
JOIN keyword ON gc.id = keyword.gc_id
WHERE keyword = 'mini'
AND keyword = '2012'
GROUP BY gc.id
ORDER BY id DESC

Obviously SQL is not my strong suit so I am looking for some help one what I am doing wrong here.

Here are my table structures:

CREATE TABLE `gift_card` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id_UNIQUE` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=52 DEFAULT CHARSET=utf8;

CREATE TABLE `keyword` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `gc_id` int(11) NOT NULL,
  `keyword` varchar(255) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id_UNIQUE` (`id`),
  UNIQUE KEY `dupes_UNIQUE` (`gc_id`,`keyword`)
) ENGINE=InnoDB AUTO_INCREMENT=477 DEFAULT CHARSET=utf8;

Consider that without the group-by or the where clause, your dataset might look like this:

(row) gc.id  gc.name  keyword.keyword
1     1      Fred     mini
2     1      Fred     tall
3     1      Fred     2011
4     2      Bob      mini
5     2      Bob      2012
6     3      Pat      2012
7     4      Kim      2011
8     4      Kim      mini
9     4      Kim      2012

The first thing that happens in your query is that the where clause evaluates each row individually to see if it matches all of the conditions.  Only after this step does it apply the grouping.  (Technically there are a few more steps involved; see Logical Processing Order of the SELECT Statement for more gory details.)
  With that in mind, though, your initial query first finds every row where the keyword is "mini", which in the sample dataset above is rows 1, 4 and 8 (card IDs 1, 2, and 4), then groups the found rows by their card ID. Your modified query is asking for every individual row where the keyword is both "mini" and "2012", in the same row.  There are rows with "mini" and there are rows with "2012", but each row has only one value or the other, not both at the same time.  In fact it is simply not possible for a single field to hold two different values at the same time.

What you need to do is to select all rows where the keyword is any one of your values, then group them by ID, and then select the IDs where the number of rows found for that ID is 2:

Select gc.id, min(gc.name)
from giftcard as GC
join keyword as KW on gc.id = kw.gc_id
where kw.keyword in ('mini','2012')
group by gc.id
having count(gc.id) = 2
order by gc.id desc

From our dataset above, this will basically go through and select rows 1, 4, 5, 6, 8, and 9; reduce that to "card 1: 1 match, card 2: 2 matches, card 3: 1 match, card 4: 2 matches", and then select "card 2, card 4" as having the required 2 matches, and produce the output

gc.id  gc.name
4      Kim
2      Bob

You could then extend this to say "any card having at least X out of these Y keywords" by adding keywords to the in clause (so it would be like  in  ('x','b','z','r','p')), and adjusting the having clause to something like having count(gc.id) >= 3.


Nesting inner to sum inner query's result Suppose I have a query select a,b from xyz

The result would be:

a          b
m1     10
m2     20
m3     30

Can I somehow sum all b's something like:

select sum(b) from (select a,b from xyz)

And it will give me result 60. Possible? Stupid question?

You need to alias your inner query and use SUM instead of COUNT.

select SUM(b) from (select a,b from xyz) temp_table;

Instead of that you can do like

SELECT SUM(b) FROM xyz;

Example

SELECT userid,score FROM user_score;
+--------+-------+
| userid | score |
+--------+-------+
|      1 |     4 |
|      1 |     5 |
|      1 |     6 |
|      1 |     7 |
|      1 |     8 |
|      1 |     9 |
|      1 |    10 |
|      1 |     2 |
|      1 |     2 |
|      2 |     2 |
|      2 |     3 |
|      2 |     4 |
|      2 |     5 |
|      2 |     6 |
|      2 |     7 |
|      2 |     8 |
+--------+-------+
16 rows in set (0.00 sec)

SELECT SUM(score) FROM (SELECT userid,score FROM user_score ) A;
+------------+
| SUM(score) |
+------------+
|         88 |
+------------+
1 row in set (0.00 sec)

OR

SELECT SUM(score) FROM user_score;
+------------+
| SUM(score) |
+------------+
|         88 |
+------------+
1 row in set (0.00 sec)


How to store count value in DB I have a table as below, order field is like auto increment, but it is not defined as auto_increment because i do not want any gaps in that value

    +--------------+
    |  id  | order1|
    +--------------+
    |  115 |     1 |
    |  116 |     2 |
    |  117 |     3 |
    |  118 |     1 |
    |  119 |     2 |
    |  120 |     6 |
    |  121 |     7 |
    |  122 |     8 |
    +--------------+

Like if i delete row with id 118  rows with id 119 should have order1 as 3 and rowId 120 should have 4  rowId 121 5

basically i want the order1 column be serial no with no gaps when there are any deletion

in your case you will have to repopulate the order column completely
because if you delete row id 118 or 119, you need to change 118,119,120 and so on till the end of table

so in this case i suggest the following using the ROW_NUMBER() function

    update YourTable
    Set [order] = NewOrder
    from
    (
    SELECT t.id,t.[order],@rownum := @rownum + 1 AS neworder
  FROM YourTable t, (SELECT @rownum := 0) r
    ) X
    where YourTable.id = X.id

so in this case if you delete 118

Delete from YourTable where id = 118

run the above update script again and then you will see that your order field is updated

Select id,[order] from YourTable


Finding empty columns of a table in PostgreSQL What query would return the name of the columns of a table where all rows are NULL?

testbed:

create role stack;
create schema authorization stack;
set role stack;

create table my_table as
select generate_series(0,9) as id, 1 as val1, null::integer as val2;

create table my_table2 as
select generate_series(0,9) as id, 1 as val1, null::integer as val2, 3 as val3;

function:

create function has_nonnulls(p_schema in text, p_table in text, p_column in text)
                returns boolean language plpgsql as $$
declare
  b boolean;
begin
  execute 'select exists(select * from '||
          p_table||' where '||p_column||' is not null)' into b;
  return b;
end;$$;

query:

select table_schema, table_name, column_name,
       has_nonnulls(table_schema, table_name, column_name)
from information_schema.columns
where table_schema='stack';

result:

 table_schema | table_name | column_name | has_nonnulls
--------------+------------+-------------+--------------
 stack        | my_table   | id          | t
 stack        | my_table   | val1        | t
 stack        | my_table   | val2        | f
 stack        | my_table2  | id          | t
 stack        | my_table2  | val1        | t
 stack        | my_table2  | val2        | f
 stack        | my_table2  | val3        | t
(7 rows)

In addition you can get an approximate answer by querying the catalog - if null_frac is zero that indicates no nulls but should be double-checked against 'real' data:

select tablename, attname, null_frac from pg_stats where schemaname='stack';

 tablename | attname | null_frac
-----------+---------+-----------
 my_table  | id      |         0
 my_table  | val1    |         0
 my_table  | val2    |         1
 my_table2 | id      |         0
 my_table2 | val1    |         0
 my_table2 | val2    |         1
 my_table2 | val3    |         0
(7 rows)


Oracle standby database recovery stopped I have a primary and an standby database.
Because my file-system became full in standby database, the recovery has stopped in standby.

This is the present state of sequences in primary and standby:

Thread Last Sequence Received Last Sequence Applied Difference

     1                    560                   394        166

How can I start the recovery again?

You then have the option of either starting a managed recovery operation or opening the database for read-only access.

Start the database:

SQL> STARTUP NOMOUNT;

Mount the standby database:

SQL> ALTER DATABASE MOUNT STANDBY DATABASE;

Start the managed recovery operation:

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE
  2> DISCONNECT FROM SESSION;


database design for accounting systems How the records are updated in an accounting system i.e., what is the standard practice?
do you keep all the records and do the calculations on the fly or you add and subtract in each time from a final amount say "$current_balance".

The first approach of doing the calculations on the fly i.e, summing up all the deposits and subtracting all the expenses is a sure way to get the result but I can see a definite scalability problem as the number of rows grow.

On the other hand accumulating in a variable say "$current_balance" and just updating it has its shortcomings as well.

So what is the recommended  practice to approach this problem?

Generally accounting systems have a large General Ledger table. Each row on the table is a transaction, functioning more like a star schema rather than a traditional 3NF relational database. In this way they are normally a hybrid of both star/snowflake and relational combining the best of both worlds where possible. They are generally not strict 3NF databases, with some duplication in the GL table in terms of project codes or business unit codes. For instance they will often look like this:

Records are not usually updated in an accounting systems. What happens instead is additional transaction rows are added which reflect the amendment in the previous entered value. For instance, if you wanted to change the Transaction Amount for BU Code A100 above to 50 instead of its current value 100, you would add a line:

If you wanted to delete that line then you would add a row similar to the above, but with -100 in the Transaction Amount (and then mark this as a reversal in the transaction types).

Generally computers are pretty fast at the adding part of this, there are various partitioning tricks you can use to improve performance. All vendors are very fixated on improving the performance, so you usually don't have an issue. With modern DBs indexing, they usually update quite fast though there are often lags.

For real performance the accounting system data is usually put into an OLAP cube, where further speed efficiencies can be obtained and more analytic type queries can be answered.


What is the "shrinkd" in SQL Server logs? I found lots of ShrinkD, ghostcleanuptask, and BTree Split/Shrink in my SQL Server logs.

At the time of these logged events, the performance counters showed that lazy writes were very slow, the log flush rate was high, and write/sec was close to 30000. Disk IO was also very high.

I'm sure that the server traffic was normal and there was no abnormal data file growth at the time, but the log file was twice as large as usual. I used the dump_log() function to get statistics on logs:

My configuration:

Windows Server 2008 sp2
SQL Server 2014
Database target_recovery_time = 60 seconds
Database auto shrink and index auto shrink = false
Log backup hourly
Physical disk: pcie ssd 3.0T with 70% used

I recently:

Cleaned up useless indexes
Reorganized fragmented indexes
Increased the automatic archiving data operation about 500w daily backup and delete

Archiving was at 4:00-5:00 very day, I suspect that the ghost cleanup is related to my physical backup deletion and archiving. The logfile was double than the same time yesterday.

My Question:

What is the "shrinkd" in SQL Server logs?
Is it shrink database?
I can't find any information about it on the Internet.If the AUTO_SHRINK set off and there is not skrink job,how did it be triggered?
Thanks.

This question was also asked in a comment on Paul Randal's blog Inside the Storage Engine: Ghost cleanup in depth

Paul replied:

  Looks like

  a) you’ve got auto-shrink enabled for the database, or there’s an Agent job doing shrink
  b) you’ve had a lot of deletes, and the shrink ‘touched’ a lot of the pages with deleted records on so they got queued up for the ghost cleanup task to take care of.

  You need to fix a).

Feedback from the question author:

  When it happened again, I set dbcc traceon(661,-1) and the server resumed immediately. Why would there be so many ghostcleanup tasks suddenly without any shrink job? The answer: if the number of pages with ghosted rows is above the specified (hard-coded) limit, the task runs non-stop till the count comes down below the threshold value.


MySql master and slave design I have set up mysql replication with single slave.
Whenever i am adding data to master database, it is automatically copied to slave database.
But if I add data to slave database it is  not available in master database. why?

If it is wrong to add slave database, how I can tell to the slave database to redirect writes to master? is it possible?

My application is designed as normal application not for replicated database. Should I  change my application code for working with replicated database?

Data should never be directly inserted into a MySQL slave [except for edge cases ignored here].  To prevent this accidentally happening you should set
    read_only
in your my.ini.  Details on this option are here: http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html#sysvar_read_only

MySQL Proxy might be an option for read/write splitting, but the last time I checked there were bugs that may mean this is currently unworkable. (MySQL Proxy: http://dev.mysql.com/downloads/mysql-proxy/, R/W split: https://wikis.oracle.com/display/mysql/MySQL+Proxy+RW+Splitting)

A third option (if you are looking for more HA than performance, and wish to avoid rewriting your application) would be to configure Master Master replication and put a loadbalancer such as haproxy in front of it - there are some excellent tutorials on the web on configuring haproxy in this kind of environment. (HAProxy: http://haproxy.1wt.eu)

(edited for spelling/links)


SSIS Excel to SQL Server - Need to add SSIS variables into the mix I have an SSIS job that loads Excel files from a UNC path into a staging SQL table, then executes a stored procedure at the SQL server to load the data into a production SQL table.

What I need to do is to ensure that along with the data from Excel, I am also loading a SSIS variable.

Say that the Excel has 10 columns. My target SQL table has 11 columns. The 11th column is for the name of the Excel file that was loaded, which I need to load alongside the Excel data.

This is an oversimplified example but it is pretty much it.

Does the Excel Connection Manager use a variable for the file name (ExcelFilePath property)?  If so, try adding a derived column with this variable as the value.  Otherwise you can add a Script Component within the Data Flow Task as follows to create a new column that will hold the file path.

Add a Script Component.  On the Input and Outputs pane, expand the Output 0 folder, select the Output Columns folder and add a new column.  Name this column as you want the column holding the file name to be named and pick the proper data type.
On the Connection Managers pane of the Script Component, add the Excel Connection Manager that is being used and provide an appropriate name for the Name property.
In the script, the following code will obtain the file name and set the new column to this value from the IDTSConnectionManager100 object.  In this example, ExcelConnection is the name that was given to the Excel Connection Manager on the Script Component editor and ExcelFilePath is the name of the new column that was added in the Data Flow.  From this point, the new column can be mapped to the corresponding column in the destination table.

IDTSConnectionManager100 ConnMnger;
ConnMnger = this.Connections.ExcelConnection;

Row.ExcelFilePath = ConnMnger.ConnectionString;


Preferred Methods of copying a new database from Development to Production I am wondering what are preferred methods to copy a new (Development) database into a new environment.
The database will be empty, so essentially I am copying only the schema of this database and not the data.

Options include:
1) SSMS (Backup/Restore). I do not use this one because it will copy test data into Production.
2) SSMS (Generate Scripts)
3) Writing T-SQL myself
4) RedGate (or other 3rd party tools out there)

Does anyone have preferences or biases?

For myself, I use most (4): I create a new empty database using SSMS, then populate the schema of new database using RedGate.

Perhaps, it makes no difference. But I would like to know from the more experienced DBAs what their opinions are. Thank you.

I'd say the best way (in my opinion, of course) is your 2nd option, SSMS (Generate Scripts).

The reason I say to script out your database is because you will have save-able scripts that you can throw into version control.  If you are only looking for the schema and no data, it's pretty simple to generate.

As for backup and restore, I agree with you.  It's a good way to port databases, but if you need to go through the pain of having a script to remove existing development data, then you are having to go that extra mile (not to mention the possibility of "user error" and not deleting all data, allowing non-prod data in production).

For the third option, writing the T-SQL yourself, that is a very manual process.  Might as well take advantage of SSMS' ability to generate a schema script for your entire database.


Data Relationship between Provinces and Cities Does anybody have experience or know how to correctly  design database relationships for where

Provinces ->hasmany cities
City ->hasOne Province

the problem is different cities (based on geography / province location) may have the same name. Thanks for the help.

How about this:

province(province_id, province_name, ...)
cities(city_id, city_name, province_id, ...)

This is (one to many) relationship. It is not affected by the fact that more than one city have the same name. Therefore, in cities table, the combination (city_name, province_id) is unique. city_name by itself is not unique.


Cannot remove Database from Distributed AlwaysOn Group on Secondary Windows Cluster We have Distributed AG across two windows clusters.

Clus01 - Has an AG set up with no DB inside it (AG1)
Clus02 - Has an AG set up with no DB inside it (AG2)
Clus01 - Has a DistAG set up with AG1 (primary) and AG2 (secondary)

We

Joined Clus02 to the DistAG
Added a database to AG1 on Clus01
Restored this DB in recovery to AG2
Added this DB to AG2 on Clus02 (Showing as Primary, but really is secondary within the DistAG)

DB on Clus02 was now working as expected...any updates on DB on Clus01 would use DistAG to update Clus02 DB.

We often refresh this environment from live (this is preprod).  so we have to restore the DB onto CLus01.

For our normal AGs, we just remove the DB from CLUS01, restore to CLUS01, backup and restore to CLUS02 and join to AG again.

For DistAG, I cannot

Remove the DB from AG2 on CLUS02.

ALTER AVAILABILITY GROUP [AG_G2CoreReporting]
REMOVE DATABASE [Genesis];
GO

We get the error

  Msg 41190, Level 16, State 8, Line 6
  Availability group 'AG2' failed to process remove-database command.  The local availability replica is not in a state that could process the command.  Verify that the availability group is online and that the local availability replica is the primary replica, then retry the command.

Remove the DB from AG1 on CLUS01.  DB on AG2 on CLUS02 goes into not syncronizing state and I cannot remove (same error as above), so cannot restore DB over the top.
Remove the replica on the DistAG by running on CLUS01

ALTER AVAILABILITY GROUP [DistAG]
REMOVE REPLICA ON N'AG2';

We get the error

  Msg 15151, Level 16, State 1, Line 5
  Cannot alter the availability group 'AG2', because it does not exist or you do not have permission.

Is there anyone that knows how I can refresh an environment with DistAG without having to remove the whole DistAG and set it up again?

You can use the below statement to remove the database from "DistAG".

Execute the below statement on primary node of "DistAG".

ALTER DATABASE Genesis SET HADR OFF;

Once you execute the above statement, the database state will turn to restoring mode, and then you can drop the database.


Adding condition to Unique Constraint I currently have the following constraint on a table:
ALTER TABLE myTable
            ADD CONSTRAINT unique_row UNIQUE (content_id, brand_id, language_id);

I want to add a condition to the constraint:  I want the constraint to work the same way, but with only a single row where is_archived is FALSE:
ALTER TABLE myTable
         ADD CONSTRAINT unique_row UNIQUE (content_id, brand_id, language_id, !is_archived);

However, when I do this I get a syntax error.
If is_archived = true then multiple rows with the same combination of content_id and brand_id are allowed.   Basically multiple rows that are the same can be archived, but only one can be unarchived.

While Postgres doesn't allow a partially unique constraint, it does support a partial unique index:
create unique index unique_row on myTable(content_id, brand_id) where not is_archived;

See Partial Indexes in the Postgres documentation.
This is effectively pretty much the same as a unique constraint, because such constraints are implemented with unique indexes anyway.


How to efficiently a query to return rows plus a summary I need to write a query for a SQL Server 2012 database that will return rows to be presented to a search results page, this page has 12 filters that the user can use to narrow the results.

I create the query that returns the rows dynamically, then I use this same query as a sub query for all the summary queries that I need. The final piece becomes a pretty big string that gets sent to the server just once, it returns a multiple result set, instead of multiple calls,  but I think there should be a more efficient way for doing this.

Right now it seems to be working ok, but I'm not sure what would happen when it goes live and hundreds of users starts to hit it.

Any suggestion or code samples would be very appreciated.

Assuming you're not return millions of rows, you could output the results into a temporary table, then run the "summary" query on that temp table.

As an example, I'll create a table with 10,000 rows of data, then return 3,333 rows, with a summary query:

USE tempdb;

IF OBJECT_ID('dbo.MyData') IS NOT NULL
DROP TABLE dbo.MyData;

CREATE TABLE dbo.MyData
(
    MyDataID int NOT NULL
        CONSTRAINT PK_Mydata
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
    , SomeData varchar(30) NOT NULL
);

;WITH cte AS (
    SELECT v.num
    FROM (VALUES(0), (1), (2), (3), (4), (5), (6), (7), (8), (9))v(num)
)
INSERT INTO dbo.MyData (SomeData)
SELECT CONVERT(varchar(30), CRYPT_GEN_RANDOM(c1.num + 1))
FROM cte c1
    CROSS JOIN cte c2
    CROSS JOIN cte c3
    CROSS JOIN cte c4;

This is the code your client would send.  It first creates a temp table, then populates the rows into the temp table, using the OUTPUT clause to return the rows immediately to the client.  Then a simple COUNT() of the rows is returned.

IF OBJECT_ID('tempdb..#MyData') IS NOT NULL
DROP TABLE #MyData;
CREATE TABLE #MyData
(
    MyDataID int NOT NULL
        PRIMARY KEY CLUSTERED
    , SomeData varchar(30) NOT NULL
);

INSERT INTO #MyData (MyDataID, SomeData)
OUTPUT inserted.*
SELECT *
FROM dbo.MyData md
WHERE md.MyDataID % 3 = 0;

SELECT COUNT(1)
FROM #MyData;

Comparing the query plans for the the two possible "summary" queries shows any possible improvements; which would only be more exaggerated with a more complicated source query:

SELECT COUNT(1)
FROM #MyData;

SELECT COUNT(1)
FROM dbo.MyData md
WHERE md.MyDataID % 3 = 0;

The plans:


SUM( A + B ) vs SUM(A) + SUM(B) I came across a script written by one of my coworkers and found a line like the following

SELECT SUM(A) + SUM(B) FROM TABLE

I proposed a different approach

SELECT SUM( A + B ) FROM TABLE

Which line is most efficient?

For the InnoDB/MySQL 5.6 table with 30M+ rows of FLOATs result is the next.

select SUM(x + y) from points;
/* Affected rows: 0  Found rows: 1  Warnings: 0  Duration for 1 query: 8,596 sec. */

select SUM(x + y) from points;
/* Affected rows: 0  Found rows: 1  Warnings: 0  Duration for 1 query: 8,799 sec. */

select SUM(x) + SUM(y) from points;
/* Affected rows: 0  Found rows: 1  Warnings: 0  Duration for 2 queries: 8,720 sec. */

select SUM(x) + SUM(y) from points;
/* Affected rows: 0  Found rows: 1  Warnings: 0  Duration for 2 queries: 8,580 sec. */

select SUM(x + y) from points;
/* Affected rows: 0  Found rows: 1  Warnings: 0  Duration for 1 query: 8,642 sec. */

In general the timings are of the same range so i suppose the difference is unsignificant.


Will a nonclustered index with a unique column always address all queries filtering that column first? I have found large heap tables that are over-indexed and for example have multiple different nonclustered indexes with different columns, but some of those indexes have the (only one present) primary key column set as their first index column.

I made some investigation, played around with indexes and queries and made my conclusions that I would like to see confirmed by professionals to be sure.

Question 1: Am I right to say it is nonsense to have multiple indexes based on the primary key instead of only ONE index based on the primary key column as the first and ONLY index column, because all queries using this pk for filtering or joining return only ONE row? (Assuming additional filtered, joined or selected columns are put into the index as included columns to entirely cover the queries and avoid RID lookups)

Question 2: Is it right to say that nonclustered indexes based on the primary key of a table should always be created as UNIQUE indexes? (a setting that is optional for whatever reason since sql server should already know that an index including the pk will be unique...)

Yes, given the constraints in the question, particularly that the primary key column is the leading column in the indexes. Also assuming the primary key never changes.
Not necessarily.

The optimizer can indeed infer uniqueness without marking the nonclustered index unique.

Marking the index unique may introduce a Split-Sort-Collapse combination in execution plans that change an index key. The extra Sort in particular has the potential to be performance-affecting.

On the other hand, not marking the index unique risks data integrity if the primary key is ever changed.

Example

CREATE TABLE dbo.Test
(
    pk integer PRIMARY KEY NONCLUSTERED,
    c1 integer NOT NULL,
    c2 integer NOT NULL
);

-- Not unique on pk, c1
CREATE NONCLUSTERED INDEX ic1 ON dbo.Test (pk, c1);

-- Unique on pk, c2
CREATE UNIQUE NONCLUSTERED INDEX ic2 ON dbo.Test (pk, c2);

Uniqueness

-- Neither plan has an aggregate
SELECT DISTINCT T.c1 FROM dbo.Test AS T WHERE T.pk = 1;
SELECT DISTINCT T.c2 FROM dbo.Test AS T WHERE T.pk = 1;

Split, Sort, Collapse

-- No split, sort, collapse
UPDATE dbo.Test SET c1 = CHECKSUM(NEWID());

-- Split, sort, collapse updating unique key
UPDATE dbo.Test SET c2 = CHECKSUM(NEWID());

Note the split-sort-collapse plan is also a wide (per-index) update.

Uniqueness is a huge topic though. I would normally mark something that is unique as unique, unless there is a good reason not to. Some further reading from my blog:

Enforcing Uniqueness for Performance
Avoiding Uniqueness for Performance
Sneaky Reads with Unique Indexes
SQL Server, Seeks, and Binary Search

To anticipate comments about heap tables: Most tables benefit from being clustered. You need good reasons to choose a heap structure, especially from a space management point of view, if the table ever experiences deletes. Updates can also introduce performance impacts if columns expand beyond the space available on the original page (forwarded records).


How to audit database user password changes in oracle 11g? I want to record how many times or which user accounts passwords were changed using Oracle database auditing techniques. I also want to record any password changes done to system user account.

Auditing can capture when a user changes their own password. If a sys or admin user changes the password of another user that can't be captured in auditing.

You can use this metalink doc (ID 419942.1) to create a system trigger and change it to add an entry in a temp table.


Undefined symbol when migrating to Postgres 11 After upgrading Postgres 10 to version 11, by using pg_upgrade, I get this error:

  could not load library "$libdir/postgis-2.4": ERROR:  could not load library "/usr/pgsql-11/lib/postgis-2.4.so": /usr/pgsql-11/lib/postgis-2.4.so: undefined symbol: geod_polygon_init

I'm using CentOS 7 and Postgis 2.4.

I've tried to investigate a little.

# nm -D /usr/pgsql-11/lib/postgis-2.4.so |grep geod_polygon_init
  U geod_polygon_init

The function is exported by the shared library!
Why am I getting this error?

# ld -v /usr/pgsql-11/lib/postgis-2.4.so
GNU ld version 2.27-28.base.el7_5.1
ld: warning: cannot find entry symbol _start; not setting start address
/usr/pgsql-11/lib/postgis-2.4.so: undefined reference to `repalloc'
/usr/pgsql-11/lib/postgis-2.4.so: undefined reference to `hash_create'
/usr/pgsql-11/lib/postgis-2.4.so: undefined reference to `get_guc_variables'
/usr/pgsql-11/lib/postgis-2.4.so: undefined reference to `init_MultiFuncCall'
/usr/pgsql-11/lib/postgis-2.4.so: undefined reference to `SPI_exec'
/usr/pgsql-11/lib/postgis-2.4.so: undefined reference to `WinGetFuncArgInPartition'
/usr/pgsql-11/lib/postgis-2.4.so: undefined reference to `SPI_gettype'
/usr/pgsql-11/lib/postgis-2.4.so: undefined reference to `geod_polygon_addpoint'
/usr/pgsql-11/lib/postgis-2.4.so: undefined reference to `get_fn_expr_argtype'
/usr/pgsql-11/lib/postgis-2.4.so: undefined reference to `GetDatabaseEncoding'
/usr/pgsql-11/lib/postgis-2.4.so: undefined reference to `SPI_execute'
/usr/pgsql-11/lib/postgis-2.4.so: undefined reference to `CurrentMemoryContext'
/usr/pgsql-11/lib/postgis-2.4.so: undefined reference to `SPI_getvalue'
...

Related questions:

https://superuser.com/questions/1267282/could-not-load-postgis-2-4-so-undefined-symbol-float8getdatum
https://www.postgresql.org/message-id/20180123173450.GK25452%40telsasoft.com

# ld -v /usr/pgsql-11/lib/postgis-2.4.so
GNU ld version 2.27-28.base.el7_5.1
ld: warning: cannot find entry symbol _start; not setting start address
/usr/pgsql-11/lib/postgis-2.4.so: undefined reference to `repalloc'
/usr/pgsql-11/lib/postgis-2.4.so: undefined reference to `hash_create'

That's not actually a problem. You can't use ld like that, I think you want to use ldd there. It's a shared object, not a executable. Without a _start (or an entry point with -e) you can't call ld and produce an executable

  could not load library "$libdir/postgis-2.4": ERROR: could not load library "/usr/pgsql-11/lib/postgis-2.4.so": /usr/pgsql-11/lib/postgis-2.4.so: undefined symbol: geod_polygon_init

That error is because I believe you're linking against a version of Proj4 that is too old. That's a distro problem if anything.


db INDEX on ORDER BY FIELD in very simple query does not help - still slow response? I have very simple query which returns 200K records very slow (20 seconds).

SELECT * FROM TABLE ORDER BY ID DESC

If I do just

 SELECT * FROM TABLE

it returns quick result.

I created INDEX on that field ID (ALLOW REVERSE SCANS) but still returns very similar response. Where can be the problem? What can be the cause of stagnation for this query? I updated statictics and index table metadata.

Shouldn't INDEX help me on this ORDER BY FIELD?

My server has 8GB RAM.
Buffer pool has value of 128MB. I adjusted it to 1 GB but still no too much progress.

I am using DB2 database.

Configuration Parameters are with this values: (SORTHEAP) = AUTOMATIC(164) and (SHEAPTHRES_SHR) = AUTOMATIC(824).  (SHEAPTHRES) = 0. Should I maybe adjust them and on what values?

This is live system in production so I will be very thankful for help.

Thank you

Well, first of all a SELECT * will pretty much void using any indexes. DB2 will see that you require the entire table and thus do a table scan (which in itself can be expensive).

After that you are doing a an ORDER BY, which causes DB2 to do a SORT on the table it just scanned (another expensive operation). So you have two expensive operations one after the other.

Another thing.....if ID is the primary key...it already built an index for you under the covers. (which again it will ignore with a SELECT *). So you now have a second index that is probably just taking up disk space. (Just as an fyi any field that has a unique constraint-including the primary key-DB2 automatically builds a unique index for you.)

So first off, what this really speaks to is a bad query (no other way to put it). Do you have any predicates to add to the SQL? If you can cut down what you are selecting with a WHERE clause, or if you don't need every column in the SELECT, then you could build an index or indexes over those columns and that would help.

I would advise you to do an EXPLAIN over this query so you can see the cost and the query plan that DB2 used. Then as you test out indexes, etc, you can see if DB2 chooses to take advantage of them or not, and which is the most efficient method.


Multicolumn indexes and OR clause I have a message table in my application and one of the most commonly executed SQL queries on this table is the following one:

select * from message message0_
 where message0_.sender_id=? or message0_.recipient_id=?
 order by message0_.send_date asc;

I am basically querying for the messages the current user has either received or sent.

The value of the parameter for sender_id & recipient_id is the current user ID.

I have created the following index:

CREATE INDEX ON message(recipient_id, sender_id);

I would like to know if the order of the fields matters for my use case bearing in mind the OR clause in my SQL query.

Can someone please help?

For your use case, you actually need two different indexes:

CREATE INDEX ON messages(recipient_id);
CREATE INDEX ON messages(sender_id);

Your multi-column indexed would be used by the recipient_id = ?? part of the query, but it would not be used by the other (it can be used by other databases that know how to do Skip Scanning, such as Oracle, although it might not be extremely efficient).

Depending on how your PRIMARY KEY is for the message(s) table, you might not need one of them (the implicity index associated with the PK will do the job).

You can then either use your query as is, and let PostgreSQL do a BitmapOr, or convert the query to a UNION and avoid the OR. Timings should be about the same in both cases. If you are sure that there are not messages where both the sender_id and the recipient_id are the same (i.e.: nobody sends a message to him/herself, or if s/he does, you don't mind showing it twice), then a UNION ALL (which is slightly quicker) could be used instead with the same result.

OR conditions tend not to be handled too well by most databases. This specific case, which is rather common and rather simple, is handled well by PostgreSQL.

Experimental check

Table definitions (simplified)

CREATE TABLE users
(
    user_id integer PRIMARY KEY,
    user_name text
) ;

CREATE TABLE messages
(
    sender_id    integer NOT NULL REFERENCES users(user_id)
                 ON UPDATE CASCADE ON DELETE RESTRICT,
    recipient_id integer NOT NULL REFERENCES users(user_id)
                 ON UPDATE CASCADE ON DELETE RESTRICT,
    send_date    timestamp NOT NULL DEFAULT now(),
    message_text text,
    PRIMARY KEY(sender_id, recipient_id, send_date)
) ;
CREATE INDEX ON messages (recipient_id) ;
-- Following index not needed, given our primary key
-- CREATE INDEX ON messages (sender_id) ;

We populate the tables with some simulated data:

-- Create 1000 users
INSERT INTO users (user_id, user_name)
SELECT
    user_id, 'user' || user_id AS user_name
FROM
    generate_series(1, 1000) AS x(user_id) ;

-- Create (aprox.) 100000 messages
INSERT INTO messages (sender_id, recipient_id, send_date)
SELECT
    (random()*999+1)::integer AS sender_id,
    (random()*999+1)::integer AS recipient_id,
    send_date
FROM
    generate_series(timestamp '2017-01-01', timestamp '2017-01-31', interval '25 seconds') AS x(send_date);

ANALYZE;

And, at this point, we check which are the execution plans that we get:

-- Checking the query with OR
EXPLAIN ANALYZE
SELECT *
FROM messages
WHERE recipient_id = 123 OR sender_id = 123
ORDER BY send_date ;

gives ...

QUERY PLAN
1   Sort  (cost=420.21..420.71 rows=200 width=48) (actual time=0.430..0.445 rows=192 loops=1)
2     Sort Key: send_date
3     Sort Method: quicksort  Memory: 34kB
4     ->  Bitmap Heap Scan on messages  (cost=10.31..412.56 rows=200 width=48) (actual time=0.092..0.389 rows=192 loops=1)
5           Recheck Cond: ((recipient_id = 123) OR (sender_id = 123))
6           Heap Blocks: exact=157
7           ->  BitmapOr  (cost=10.31..10.31 rows=200 width=0) (actual time=0.061..0.061 rows=0 loops=1)
8                 ->  Bitmap Index Scan on messages_recipient_id_idx  (cost=0.00..5.04 rows=100 width=0) (actual time=0.033..0.033 rows=97 loops=1)
9                       Index Cond: (recipient_id = 123)
10                ->  Bitmap Index Scan on messages_pkey  (cost=0.00..5.17 rows=100 width=0) (actual time=0.025..0.025 rows=95 loops=1)
11                      Index Cond: (sender_id = 123)
12  Planning time: 0.379 ms
13  Execution time: 0.527 ms

And using UNION:

-- Not using OR, but UNION-ing
EXPLAIN ANALYZE
SELECT *
FROM messages
WHERE recipient_id = 123
UNION
SELECT *
FROM messages
WHERE sender_id = 123
ORDER BY send_date ;

you get the following query plan:

QUERY PLAN
1   Sort  (cost=538.87..539.37 rows=200 width=48) (actual time=0.387..0.399 rows=192 loops=1)
2     Sort Key: messages.send_date
3     Sort Method: quicksort  Memory: 34kB
4     ->  HashAggregate  (cost=529.22..531.22 rows=200 width=48) (actual time=0.268..0.317 rows=192 loops=1)
5           Group Key: messages.sender_id, messages.recipient_id, messages.send_date, messages.message_text
6           ->  Append  (cost=5.07..527.22 rows=200 width=48) (actual time=0.038..0.192 rows=192 loops=1)
7                 ->  Bitmap Heap Scan on messages  (cost=5.07..262.55 rows=100 width=48) (actual time=0.038..0.094 rows=97 loops=1)
8                       Recheck Cond: (recipient_id = 123)
9                       Heap Blocks: exact=89
10                      ->  Bitmap Index Scan on messages_recipient_id_idx  (cost=0.00..5.04 rows=100 width=0) (actual time=0.022..0.022 rows=97 loops=1)
11                            Index Cond: (recipient_id = 123)
12                ->  Bitmap Heap Scan on messages messages_1  (cost=5.19..262.67 rows=100 width=48) (actual time=0.033..0.085 rows=95 loops=1)
13                      Recheck Cond: (sender_id = 123)
14                      Heap Blocks: exact=86
15                      ->  Bitmap Index Scan on messages_pkey  (cost=0.00..5.17 rows=100 width=0) (actual time=0.021..0.021 rows=95 loops=1)
16                            Index Cond: (sender_id = 123)
17  Planning time: 0.178 ms
18  Execution time: 0.521 ms

For the sake of completeness: UNION ALL:

    QUERY PLAN
1   Sort  (cost=537.11..537.62 rows=201 width=48) (actual time=0.213..0.229 rows=160 loops=1)
2     Sort Key: messages.send_date
3     Sort Method: quicksort  Memory: 32kB
4     ->  Append  (cost=5.08..529.42 rows=201 width=48) (actual time=0.034..0.173 rows=160 loops=1)
5           ->  Bitmap Heap Scan on messages  (cost=5.08..264.74 rows=101 width=48) (actual time=0.034..0.086 rows=82 loops=1)
6                 Recheck Cond: (recipient_id = 123)
7                 Heap Blocks: exact=77
8                 ->  Bitmap Index Scan on messages_recipient_id_idx  (cost=0.00..5.05 rows=101 width=0) (actual time=0.022..0.022 rows=82 loops=1)
9                       Index Cond: (recipient_id = 123)
10          ->  Bitmap Heap Scan on messages messages_1  (cost=5.19..262.67 rows=100 width=48) (actual time=0.030..0.075 rows=78 loops=1)
11                Recheck Cond: (sender_id = 123)
12                Heap Blocks: exact=72
13                ->  Bitmap Index Scan on messages_pkey  (cost=0.00..5.17 rows=100 width=0) (actual time=0.020..0.020 rows=78 loops=1)
14                      Index Cond: (sender_id = 123)
15  Planning time: 0.222 ms
16  Execution time: 0.280 ms

You can see that, in both cases, the execution plan is nearly the same for the two firsts approaches, and better for the UNION ALL.

You can check all this at Rextester.

Your original setup

If you do exactly the same but don't have the CREATE INDEX ON messages (recipient_id) ; statement, what you would get is:

QUERY PLAN
1   Sort  (cost=2123.86..2124.36 rows=200 width=48) (actual time=27.305..27.320 rows=227 loops=1)
2     Sort Key: send_date
3     Sort Method: quicksort  Memory: 35kB
4     ->  Seq Scan on messages  (cost=0.00..2116.22 rows=200 width=48) (actual time=0.108..27.097 rows=227 loops=1)
5           Filter: ((recipient_id = 123) OR (sender_id = 123))
6           Rows Removed by Filter: 103454
7   Planning time: 0.474 ms
8   Execution time: 27.392 ms

QUERY PLAN
1   Sort  (cost=2135.60..2136.10 rows=201 width=48) (actual time=15.716..15.803 rows=227 loops=1)
2     Sort Key: messages.send_date
3     Sort Method: quicksort  Memory: 35kB
4     ->  HashAggregate  (cost=2125.90..2127.91 rows=201 width=48) (actual time=15.571..15.621 rows=227 loops=1)
5           Group Key: messages.sender_id, messages.recipient_id, messages.send_date, messages.message_text
6           ->  Append  (cost=0.00..2123.89 rows=201 width=48) (actual time=0.061..15.371 rows=227 loops=1)
7                 ->  Seq Scan on messages  (cost=0.00..1857.01 rows=100 width=48) (actual time=0.060..15.092 rows=117 loops=1)
8                       Filter: (recipient_id = 123)
9                       Rows Removed by Filter: 103564
10                ->  Bitmap Heap Scan on messages messages_1  (cost=5.20..264.87 rows=101 width=48) (actual time=0.086..0.248 rows=110 loops=1)
11                      Recheck Cond: (sender_id = 123)
12                      Heap Blocks: exact=101
13                      ->  Bitmap Index Scan on messages_pkey  (cost=0.00..5.18 rows=101 width=0) (actual time=0.066..0.066 rows=110 loops=1)
14                            Index Cond: (sender_id = 123)
15  Planning time: 0.333 ms
16  Execution time: 16.006 ms

QUERY PLAN
1   Sort  (cost=2131.58..2132.08 rows=201 width=48) (actual time=14.847..14.865 rows=227 loops=1)
2     Sort Key: messages.send_date
3     Sort Method: quicksort  Memory: 35kB
4     ->  Append  (cost=0.00..2123.89 rows=201 width=48) (actual time=0.076..14.731 rows=227 loops=1)
5           ->  Seq Scan on messages  (cost=0.00..1857.01 rows=100 width=48) (actual time=0.076..14.497 rows=117 loops=1)
6                 Filter: (recipient_id = 123)
7                 Rows Removed by Filter: 103564
8           ->  Bitmap Heap Scan on messages messages_1  (cost=5.20..264.87 rows=101 width=48) (actual time=0.082..0.209 rows=110 loops=1)
9                 Recheck Cond: (sender_id = 123)
10                Heap Blocks: exact=101
11                ->  Bitmap Index Scan on messages_pkey  (cost=0.00..5.18 rows=101 width=0) (actual time=0.060..0.060 rows=110 loops=1)
12                      Index Cond: (sender_id = 123)
13  Planning time: 0.284 ms
14  Execution time: 14.931 ms

... which are much worse query plans, because you need some sequential scans. You can check this approach also at this Rextester.


MongoDB date mismatch I have a .Net MVC app that, when submitted, captures the current DateTime in a jTable. The information in the jTable in populated from the MongoDB that the information hits when the app is submitted.

The problem I am having, is that when I go to export the data, it is showing the date -1 information for the current-day column. When I launch Studio 3T for MongoDB, the "DateCreated" column has date -1, but, when I hover the cursor over the row containing the "_id," it shows the correct, current date.

Is there a way to correct this that I am missing?

Thank you!

Please note that your date field actually isn't a date field but a string field (you can see this by the type icon next to the value). It seems that in the export it gets converted to a simple string. I hope this helps!

(Disclaimer: I work for the company who makes Studio 3T)


Interpreting an execution plan I need some help analysing some simple t-sql execution plans.
I have a table [User] with the following 3 columns:

Id(Primary Key), Username(nvarchar(50)), FirstName(nvarchar(50))

I have Created an Unique NonClustered Index for the Username column

The execution plan for a query filtering by FirstName:

select * from [User] where FirstName ='John'

shows a Clustered Index Scan with a total cost of 0,0033095

The execution plan for another query filtering by Username:

select * from [User] where Username = 'johndoe'

shows a nested loop containing an Index Seek followed by a Clustered Index Seek with a total cost of 0,00657038! this is twice as much as the cost of the previous query and I don't get it!

There is an index associated with the Username column and no index associated with FirstName, I would expect the second query to be faster.

Total cost is almost always not reliable for things like this

I would use STATISTICS IO and STATISTICS TIME instead

run both queries and look at the difference in reads

SET STATISTICS IO ON
select * from [User] where FirstName ='John'
select * from [User] where Username = 'johndoe
SET STATISTICS IO OFF

and you can also look at the time by using this

SET STATISTICS TIME ON
select * from [User] where FirstName ='John'
select * from [User] where Username = 'johndoe'
SET STATISTICS TIME OFF


Is it dangerous to expose MongoDB's db.collection.find to users? I know in SQL land people love referring to Little Bobby Tables as the main reason to have a very strict interface between your user base and your database.

However, I want to implement a robust query solution using MongoDB, and it seems like I can theoretically give a user an interface that grants them full reign to what they want to query for.

The only danger I see is a user creating a long running query, which can be mitigated by using $maxTimeMS with my db.collection.find query. Am I being naive?

I figure since db.collection.find forces queries to a particular collection, users would only have access to that collection. So they could only search in the websites collection, but not in the users collection.

The real answer is it depends, but most of the time this probably isn't a good idea. SELECT while it is less nefarious than other the other CRUD commands still carries risk.
Why this is probably a bad plan

Most applications with databases have some concept of an authenticated user vs a non-authenticated user. If I have unrestricted SELECT only access to the database I can find your hashed passwords and brute force the password offline.
I can mine your database for your user's contact information to let them know about my buddy who is an African prince with a problem accessing his funds.
I can DDoS your server by crafting long running queries and repeating them every timeout.
I can create lots of memory intensive queries to drain resources as well
I can scrape your data and use it for my own profit.
I can increase your bandwidth costs by repeatedly requesting large amounts of data
And many more things

It is important to note that even sites like Have I been pwned? which offer unauthenticated searches of large semi-public data sets don't allow unrestricted SELECT capabilities.


Default trace vs auditing in SQL Server Currently I am working in an environment that has the default trace enabled and I would like to propose that we add SQL server auditing to our environment.
I'm looking for some points to put forward to our more senior database members and to management who think that the default trace is enough.

What is the difference between these two different solutions?
Is there any advantages/disadvantages of using one over the other?

Default trace is just a collection of behavioral information about the system. It's not the same as auditing. They do different things and support different purposes. One doesn't supplant the other.
Further, default trace is actually inferior to another thing running on all systems by default, the system_health Extended Events session. In terms of information gathered and load on the system, system_health is a better alternative to the default trace. The information there is all about errors, deadlocks, long wait times, long running queries, stuff like that. It's not about security and change auditing.
SQL Server Audit is using Extended Events under the covers as a mechanism for tracking security, change auditing, and the rest of stuff that's needed for auditing your servers. That's distinct from the default trace or system_health.
So, I'd take it to your more senior people that, you probably need both. The good news is, system_health is already there. Now, implementing auditing carries with it quite a bit of work and some changes in how you do deployments and other things. You'll want quite a bit of testing before you implement it.


Removing locks in Oracle without being admin 3 wild sessions each got a row exclusive lock in the same table:

SELECT * FROM DBA_DML_LOCKS;

How can I remove the lock without being admin?

If I was admin I would list (SID, SERIAL#) of the sessions that have the locks:

SELECT SID,SERIAL#
FROM V$SESSION
WHERE SID IN (SELECT SESSION_ID
FROM DBA_DML_LOCKS );

Then execute:

ALTER SYSTEM KILL SESSION 'SID,SERIALl#';

e.g.:

ALTER SYSTEM KILL SESSION '115,1931';

But since I am not admin I get an insufficient privilege error:

ALTER SYSTEM KILL SESSION '115,1931'
Error report -
SQL Error: ORA-01031: insufficient privileges
01031. 00000 -  "insufficient privileges"
*Cause:    An attempt was made to perform a database operation without
           the necessary privileges.
*Action:   Ask your database administrator or designated security
           administrator to grant you the necessary privileges

I am running Oracle 11gR2 11.2.0.3.

If this happens often there is an option to write a package or a stored procedure for that. The owner of the package would need the alter system privilege and the package should be created with definers rights.
You just need execute privileges on that package and feed the session kill procedure with the right parameters.
There is still a good chance that zombie processes keep running until the end user that was running the killed processes tries to do anything. These zombies kan be killed from the OS. To find those processed, before killing read their os pids by joining v$session with v$process. An other option is to regularly clean those orphaned processes by finding processes in v$process that have no session in v$session.


Converting a large SQL Server database to MySQL. Database corruption? Can't access data after transferring I have gigabytes of tabular data stored in a SQL Server database. I've been trying to migrate it to MySQL using the Migration Toolkit from Oracle. I didn't have any mapping errors and fixed all the syntax mismatches, so now the rows are being transferred. But when I go to PHPMyAdmin and try to browse, it keeps saying MySQL returned an empty result set (i.e. zero rows). ( Query took 17.0116 sec ) although it says there are 44,546,089 rows in that table, and I can see the hard drive space filling up too. I just can't access any of the data.

I researched a bit and increased the buffer size from 16M to 160M in my.conf. But I can't fix this problem.

Some things I noticed (red marked in the status page):

Innodb_buffer_pool_pages_dirty: 376

Innodb_buffer_pool_reads = 92 k

Handler_read_rnd = 41

Handler_read_rnd_next = 59 k

Any advice? This database will be read-only. Also the server specs:  dedicated, 8GB RAM, Debian 6, 500GB HDD, ~2.8GHz Quadcore Intel

As you have a dedicated server, there is no reason not to use native MySQL command to issue queries directly. Avoiding phpMyAdmin means avoiding one more layer between you and database while trying to figure out what went wrong.
My first thought is - are you actually trying "select * from table" on a 45 million row table? That could cause all kinds of problems.

1) Try "select count(*) from table" first. See if you get 0 or 45M.

2) See if partition with your data is large enough. Yes, this kind of omission happens.

3) Try using MyISAM instead of InnoDB to reveal possible server misconfiguration issue.

Perhaps it makes sense to use our battle-proven commercial database migration tool Full Convert Enterprise for your data conversion? It runs on Windows only, though. Quickest path


Paritioning performance for full table scan on non-paritioned index We have a table in Postgres that we are considering partitioning. We occasionally query an indexed field that is separate from one with the partition key.

My question is: Can partitioning the table make this query faster?

My instinct says it no. Instead of scanning one index we will now be scanning multiple indexes. However, another developer I work with insists that the query will be faster because instead of scanning one large index, the database will scan multiple smaller tables in parallel.

instead of scanning one large index the database will scan multiple smaller tables in parallel.

PostgreSQL does not currently support parallel query (though hopefully it isn't far away), so it will be digging through your partitions essentially one-at-a-time if you are querying based on something other than the exclusion constraint for your partitions.

It'll be up to you to test whether querying N small indexes works out faster than querying one large one for your particular use-case. FWIW, folk wisdom says probably not, but YMMV,  in particular you may do a better job vacuuming and keeping bloat down on several small partitions and indexes than with one giant table plus index(es), for instance.

See this wiki page for a starting point about the state of Parallel Query in Postgres and workarounds.


Update two tables, each on separate MySQL servers, on two different machines in one query? Have two MySQL servers running on two different machines in a local network. Using PHP and accessing via IP. No problems there. The databases have identical schemas. One was an import from the other machine.

Want to run ONE query that updates the same table on each server simultaneously. Already have existing queries and do not want to update them extensively. Is there a simple way to do this?

I'm looking to keep the databases in sync when an update occurs.

Also open to any suggestions regarding another way to keep the databases in sync.

If you update one server only, master/slave replication is sufficient, otherwise group replication or galera


Primary file after table partitioning - SQL Server 2014 I have a 230GB database without a clustered index that I'm trying to partition into 25 files for efficiency on a hard drive with 460GB of space. I've created the partition function and partition scheme, and I have executed the commands. During the partition process, the 25 files grew to about 230GB as well and the Primary file did not shrink causing the drive to run out of room. Although the query finished, I was left with two errors.

Transaction log disappeared since there was no room due to ACTIVE_TRANSACTION
Could not allocate a new page for database because of insufficient disk space.

I'm trying to figure out if:

the partition is successful and that this is an indexing issue.
the Primary is still taking up the full 230GB or is it just allocated space that appears used on my drive. If so, can I safely shrink this without fragmentation?
there is an issue with the missing transaction log. If not, where is it going?

I've been able to run queries against the database, so it seems fine on the surface. However, I cannot find any more information on the above mentioned problems. In theory, SQL cannot operate without a transaction log, so I am not sure how to proceed.

I only have a single table Europe_Amad which has 130 columns. No primary keys set.

My partition script is as follows:

USE [FTC]
GO
BEGIN TRANSACTION
CREATE PARTITION FUNCTION [FTC_to_NACE](nvarchar(255)) AS RANGE RIGHT FOR VALUES (N'3500', N'3600', N'4100', N'4500', N'4800', N'5500', N'5800', N'6400', N'6800', N'6900', N'7700', N'8400', N'8500', N'8600', N'9000', N'9400', N'9700', N'9900')

CREATE PARTITION SCHEME [FTC_PS1] AS PARTITION [FTC_to_NACE] TO ([FTCC], [FTCD], [FTCE], [FTCF], [FTCG], [FTCH], [FTCI], [FTCJ], [FTCK], [FTCL], [FTCM], [FTCN], [FTCO], [FTCP], [FTCQ], [FTCR], [FTCS], [FTCT], [FTCU])

SET ANSI_PADDING ON

CREATE CLUSTERED INDEX [ClusteredIndex_on_FTC_PS1_636522495640236401] ON [dbo].[Europe_Amad]
([NACE_PRIM_CODE])WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [FTC_PS1]([NACE_PRIM_CODE])

DROP INDEX [ClusteredIndex_on_FTC_PS1_636522495640236401] ON [dbo].[Europe_Amad]

COMMIT TRANSACTION

This might not be a completed answer but I cannot put all these in comment section.  Once you edit your question with more information I will edit the answer if required.

I am sure you did not get the exact error message you mentioned in the question. Transaction log disappeared since there was no room due to ACTIVE_TRANSACTION --Did you mean you transaction could not be completed because log file ran out of space?

  the partition is successful and that this is an indexing issue.

Use below query to find out details about your partitions.

SELECT
    OBJECT_NAME(SI.object_id) AS PartitionedTable
    , DS.name AS PartitionSchemeName
    , PF.name AS PartitionFunction
    , P.partition_number AS PartitionNumber
    , P.rows AS PartitionRows
    , FG.name AS FileGroupName
FROM sys.partitions AS P
JOIN sys.indexes AS SI
    ON P.object_id = SI.object_id AND P.index_id = SI.index_id
JOIN sys.data_spaces AS DS
    ON DS.data_space_id = SI.data_space_id
JOIN sys.partition_schemes AS PS
    ON PS.data_space_id = SI.data_space_id
JOIN sys.partition_functions AS PF
    ON PF.function_id = PS.function_id
JOIN sys.destination_data_spaces AS DDS
    ON DDS.partition_scheme_id = SI.data_space_id
    AND DDS.destination_id = P.partition_number
JOIN sys.filegroups AS FG
    ON DDS.data_space_id = FG.data_space_id
WHERE DS.type = 'PS'
AND OBJECTPROPERTYEX(SI.object_id, 'BaseType') = 'U'
AND SI.type IN(0,1)
ORDER BY PartitionedTable, partitionnumber

  the Primary is still taking up the full 230GB or is it just allocated
  space that appears used on my drive. If so, can I safely shrink this
  without fragmentation?

Run below query to find out how much of 230GB is being used/free.

SELECT  NAME = LEFT(a.NAME, 64) ,
    a.FILEID ,
    [FILE_SIZE_MB] = CONVERT(DECIMAL(12, 2), ROUND(a.size / 128.000, 2)) ,
    [SPACE_USED_MB] = CONVERT(DECIMAL(12, 2), ROUND(FILEPROPERTY(a.name,
                                                          'SpaceUsed')
                                                    / 128.000, 2)) ,
    [FREE_SPACE_MB] = CONVERT(DECIMAL(12, 2), ROUND(( a.size
                                                      - FILEPROPERTY(a.name,
                                                          'SpaceUsed') )
                                                    / 128.000, 2)) ,
    [PERCENT_FREE] = CONVERT(DECIMAL(12, 2), ( CONVERT(DECIMAL(12, 2), ROUND(( a.size
                                                          - FILEPROPERTY(a.name,
                                                          'SpaceUsed') )
                                                          / 128.000, 2))
                                               * 100 )
    / CONVERT(DECIMAL(12, 2), ROUND(a.size / 128.000, 2))) ,
    FILENAME = LEFT(a.FILENAME, 66)
    FROM    dbo.sysfiles a

  there is an issue with the missing transaction log. If not, where is
  it going?

If your database is online (as you mentioned you are able to query the database) your transaction log file is not missing.


What significance does an index_id < 256000 have? In a certain tutorial I read the author is filtering sys.indexes based on the predicate index_id < 256000. What does this accomplish?

This is based on the misconception that XML indexes are currently the only type that could ever have an id scheme that is >= 256000 (at least based on their observation; this scheme is not documented AFAIK, so not even sure if it's intentional). Probably fine in current versions, but who knows what type of index will be added next and where its id scheme will start? If you want to exclude XML indexes, you are now also excluding something else. Spatial indexes, for example, seem to start at id = 384000. If the query above is intending to include spatial indexes but not XML indexes, they're going to be in for a surprise.

A much better filter would be:

WHERE type <> 3;

...or even better, since it is self-documenting...

WHERE type_desc <> N'XML';

And now when you want to also exclude, say, spatial indexes, your query changes to...

WHERE type_desc NOT IN (N'XML', N'SPATIAL');

...instead of having to figure out what numeric range the id values for spatial indexes might occupy (or not). Good luck with that.

These are pretty clearly documented in sys.indexes (Transact-SQL). I see no reference to this magic number and I highly recommend you point your tutorial author here so they can see that this magic number is not something they should be relying on (never mind teaching others to rely on).


Managing Transaction Log Size using a Maintenance Plan Are there any best practices for configuring a Maintenance Plan in SQL Server 2008?  Currently, I'm removing database backups and transaction logs greater than 40 hours old, then backing them up.  Problem I've seen is that the transaction log is still very large.  Should I be including a Shrink Database Plan task?

If your log file is growing that large then you are doing at least one of these things wrong:

if you need point-in-time recovery, then you are not backing up the log frequently enough (and perhaps the database too). Perhaps you have created a maintenance plan to back up the database daily or weekly, but haven't configured a maintenance plan that backs up the log.
if you don't need point-in-time recovery, then you are in full recovery model but shouldn't be. You should be able to switch the recovery model for your database(s) to simple. This will mean that you will only be able to recover to the time of the last full or differential backup, but it also means your log file will stay in check on its own.
in either recovery model, the problem may be that you have an extremely long-running transaction. As in, someone called BEGIN TRANSACTION, locked their workstation, and went on vacation.

A lot more details about this can be seen in this question:

Why Does the Transaction Log Keep Growing or Run Out of Space?

In order to shrink the log for now, if you are in full recovery model and want to stay that way, you should perform these steps and restart your log chain to be safe:

USE [master];
GO
ALTER DATABASE db_name SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO
-- if you are already in simple, remove this line:
ALTER DATABASE db_name SET RECOVERY SIMPLE;
GO
CHECKPOINT;
GO
-- if you're already in simple, or want to stay that way, remove this line:
ALTER DATABASE db_name SET RECOVERY FULL;
GO
BACKUP DATABASE db_name TO DISK = 'path' WITH INIT, COMPRESSION;
GO
USE db_name;
GO
DBCC SHRINKFILE(N'db_name_log', 30); -- pick some size that makes sense
GO
USE [master];
GO
-- if recovery is full, re-init log chain, otherwise remove this line:
BACKUP LOG db_name TO DISK = 'path' WITH INIT, COMPRESSION;
GO
ALTER DATABASE db_name SET MULTI_USER;
GO

I commented lines that should be removed if you're already in simple (highly unlikely) or if you want to switch to simple.


Trigger to track changes In SQL Server, version 2008R2 Enterprise, 64 bit, we created a database to track DDL changes.
I was inspired by this solution: https://www.mssqltips.com/sqlservertip/2085/sql-server-ddl-triggers-to-track-all-database-changes/

Then we created a trigger, on each database, like this:

CREATE TRIGGER [CaptureChanges] ON DATABASE
FOR CREATE_PROCEDURE, ALTER_PROCEDURE, DROP_PROCEDURE
 AS ....

Where, after the as, the code make a simple insert in the audit database.

At first, it works very well, but when a user, owner of a database, but with no permission on the audit database, tries to alter a stored, SQL Server rises an error for insufficient permission.This is correct, because the user ha not permissions on the audit database.

So I used EXECUTED AS using a user with sysadmin role:

CREATE TRIGGER [CaptureChanges]  ON DATABASE
WITH EXECUTE AS 'useradmin'
FOR CREATE_PROCEDURE, ALTER_PROCEDURE, DROP_PROCEDURE

But now it does not work because this error:

Msg 916, Level 14, State 1, Procedure CaptureChanges, Line 0 [Batch Start Line 0]
The server principal "useradmin" is not able to access the database "userdatabase" under the current security context.

It seems, when you use EXECUTED AS, you are sandboxed to a single database: http://www.sommarskog.se/grantperm.html#EXECUTE_AS

So, I cannot use EXECUTED AS, because it does not work cross db. I dont like the idea to create the table to track changes in every DB where a user can be dbowner.

I can assign permission to every user to write in the destination table, but then I need to remember this for every new user. Someone will sure forgot this.

I also considered a trace, but it can be easly stopped or it will not restart at server start.

Is the trigger the wrong way? Can I fix this in some way?

I also considered a trace, but it can be easly stopped or it will not
  restart at server start.

You can have a server side trace as a SQL Agent Job and set up the job to start whenever SQL agent start and a second schedule to start every xx number of minute.  That way job will always start and second schedule will take care if somebody stop it by mistake.

  Is the trigger the wrong way? Can I fix this in some way?

I suggest you create a database role and make all the users member of that role. I understand your concern of forgetting to add users to the role or assign permission. I personally do not prefer to have too many users with privilege to change schema in my database so the list should be small and a role will make it easy for you to manage the privilege.


Index on integer column not helping. Help? (EXPLAIN ANALYZE output inside) The table market_trades has an integer column market_id which takes values from 1 to 5. This seems like a pretty straightforward query to get the number of trades on each market:

WITH markets AS (
    SELECT unnest('{1,2,4}'::int[]) AS market_id
)
SELECT m.market_id, count(td.id) AS agg
FROM markets m
LEFT JOIN market_trades td
ON td.market_id = m.market_id
GROUP  BY m.market_id
ORDER  BY m.market_id;

But it takes about 10 full seconds to run! I added an index on market_trades.market_id, but it did not speed up the query at all.

The market_trades table has about 30 million rows.

Here is the ouput of EXPLAIN ANALYZE on the above query (on depesz):

                                                                    QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------
 Sort  (cost=1142468.03..1142468.28 rows=100 width=8) (actual time=62052.515..62052.516 rows=3 loops=1)
   Sort Key: m.market_id
   Sort Method: quicksort  Memory: 25kB
   CTE markets
     ->  Result  (cost=0.00..0.51 rows=100 width=0) (actual time=0.006..0.010 rows=3 loops=1)
   ->  HashAggregate  (cost=1142463.20..1142464.20 rows=100 width=8) (actual time=62052.502..62052.504 rows=3 loops=1)
         ->  Hash Right Join  (cost=3.25..992529.47 rows=29986746 width=8) (actual time=1.398..51289.914 rows=14297964 loops=1)
               Hash Cond: (td.market_id = m.market_id)
               ->  Seq Scan on market_trades td  (cost=0.00..580208.46 rows=29986746 width=8) (actual time=0.007..21659.649 rows=29985911 loops=1)
               ->  Hash  (cost=2.00..2.00 rows=100 width=4) (actual time=0.023..0.023 rows=3 loops=1)
                     Buckets: 1024  Batches: 1  Memory Usage: 1kB
                     ->  CTE Scan on markets m  (cost=0.00..2.00 rows=100 width=4) (actual time=0.010..0.017 rows=3 loops=1)

EDIT: Clearly the index on market_trades.market_id is not being used. Why not?

With only 5 values the index isn't honing to be very selective for joining so it'll pretty much always index scan unless looking for just one value.

That query plan/anal output suggests that it thinks half the work is in matching the rows there with the CTE, could you try run the group+aggregate over the whole lot. If it is index scanning anyway you might find it faster to do that and then filter out the two you are not looking for results for.


SQL Server - SQL Agent - Allow a Group to own a job Scenario
There are three separate groups of users in our organisations (development, support, implementation). As it stands everyone is using a single login for everything. We are starting to move away from this. Everyone is accessing the database though their own login where each person has access to their groups systems. So Support has access to support functions and jobs etc.
The allowed permission for the procedures and tables are really easy. Create a login from the windows group. Then create an user based on that login and allow it to read and execute on the new schema that we're creating for them.
We are running SQL Server 2012 and 2008 R2.
Problem
The SQL Agent Jobs sits above the user level and requires a login to be assigned to the owner. But it doesn't take a group login as an accepted parameter:

The specified @owner_login_name is invalid (valid values are returned by sp_helplogins [excluding Windows NT groups]).

The hope is to allow the groups to have their own set of jobs that they can manipulate directly (on a reporting server not the live database). Without effecting others departments work or the other jobs (DB maintenance tasks, replication etc). But allowing other members of a team to manipulate a job if the original writer is out of the office or has left.
I've tried searching around but so far the only thing I've found on sqlservercentral.com from back in 2010 saying it can't be done.
Things I'm looking at
Having the SQL Agent job assigned to a support / devopment  generic account that nobody has access to. But everyone somehow has access to manipulate their jobs.
This would allow people to alter the group's jobs while allowing jobs to continue being used after an individual leaves the company.
I've not found a way for people to be able to access / run jobs by that have other owners (even when they are using the same windows group login to sql). Without granting them SQLAgentOperateorRole (which would defeat the point in locking them out) of each other's (and the admin's) jobs.
Create a user per group that has the added access of the msdb rights along with the standard support abilities that they can log into to manipulate jobs.
The disadvantage of this is now the users have two logins one of which won't change. So an user leaving the company would still have the login credentials for that account potentially, it would also lead to users being lazy and only ever logging in under that account. (They shouldn't but let's be honest, they will).
Having a SQL job that runs every 5 minutes, each group has a table with commands for run this command at frequency x (essentially one SQL agent job runs a custom written SQL Agent list of jobs).
I don't think I need to go through how horrible an idea this is. But it's something that has semi been considered.
Overview
Obviously the best solution that we can see for us, would be to allow a group to own a SQL Agent job but it doesn't seem like this is possible.
Anyone have any ideas? For now guidance on how to implement something like this would be appreciated.

Since SQL Server Agent does not support using a Windows Group as the owner of a SQL Server Agent job. You can do it, but it means an end run around the built in restrictions.

You can use some code that is posted at the link below.

Allow non-sysadmin, non-owner of a SQL Server Agent job to execute it

This approach does an 'end run' around the "owner" of the SQL Agent job for all practical purposes of execution.  This is because the dbo.StartAgentJob stored procedure uses the Logins and Groups in the dbo.msdbJobMap table to decide who can run a SQL Agent Job.  As written, it will include any member of a group assigned to the job, has the rights to start the job.

This means that anyone with rights to create a SQL Server Agent Job (a member of SQLAgentReaderRole or higher) can create and modify their own josb.

If they also have rights to insert the proper GroupName and JobName in the dbo.msdbJobMap table, anybody in those groups assigned to the job can execute the job as well.

Although a GROUP does not own the job directly, the dbo.StartAgentJobstored procedure will use its elevated permissions to determine whether to allow the Login and/or members of a Group to execute this job.

What still remains as a problem?

The owner of the SQL Agent Job is the only one who can edit the job contents.  (Of course, a sysadmin can do almost anything.)

If the SQL Agent job needs to be rewritten and the owner no longer exists in your system, the sysadmin can change the job owner from the defunct account to the new designated owner. (Or script the job out and give the script to the developer who will do the rewrite and give the new job a new name.)

So, it is not a 100% solution to make the developers completely independent from the administrators, but it should Greatly Reduce the interactions needed.

Footnote: If the job is to run on distributed servers this is not enough.


how are passwords encoded in pg_shadow How are passwords encoded in column passwd of pg_shadow?

Doing a:

select passwd from pg_shadow;

I see values like the following:

 md52c92c858aa1fa12144f1dcbd4ca9c3a0

... which, doesn't seem to be what I know my password's MD5 hash to be (stripping the leading "md5" obviously). Are the hashes also salted?

It actually suffixes the password with the username and then MD5 hash is made out of that, and that hash is then prefixed with 'md5'


How Graph Databases Store Data on Disk I have seen these papers:

TurboGraph
GraphChi
BiShard Parallel Processor
G-Store
NXgraph
GridGraph

...and a few others. I am new to databases (other than having used them for web apps without understanding their internals), so I don't have much of a foundation on how to use the disk for storage.

I would like to know in general what these papers are doing for storing the graph on the file system. In taking a brief glance at them, they mention loading relevant subgraphs into memory from disk for efficient updates/querying. Some of them store the edges in one set of files (calling them "shards") and the vertices in another set of files (calling them "intervals"). Some have multiple different IDs, such as the "record ID (RID)" and "vertex ID" in TurboGraph (Figure 1 below).

However, I haven't yet seen a full overview of how all the pieces fit together. Wondering if one could explain that.

Specifically:

How the data might be structured into files for a graph database (at a high level).
What must happen when querying/updating in terms of loading stuff into memory at a high level.

It is unclear to me so far what needs to be loaded into memory, and what specifically the IDs are for. I'm not sure if each page (>= 1MB typically) is loaded into memory and parsed somehow, or scanned by line-by-line, or something similar (basically not sure how the file is parsed/scanned, and if it is parsed into some sort of in-memory data structure or if you can do the graph traversal stuff directly on the file bytes). And I'm not sure what the IDs are for. In RDBMSs the ID is sometimes an incremented integer per table without other meaning. In these papers it seems the ID has more to do with the position of a vertex in a page plus some sort of offsets and such. Also, some papers store a large single line (it seems) for a vertex with all its edges (an adjacency list), but I wonder about if you have thousands or millions of edges per vertex what to do. If one could point out the relevant features to look for then doing further research would help bring this into more clarity.

Thank you so much for your time, I hope that makes sense.

Figure 1.

I believe every implementation of a graph will vary on how they go about writing and reading from the disk.
On page 2, section 2.2 of Dgraph: Synchronously Replicated, Transactional and Distrubuted Graph Database by Manish Jain, Data Storage discussion is introduced with:

Dgraph data is stored in an embeddable key-value database
called Badger for data input-output on disk. Badger is
an LSM-tree based design, but differs from others in how it
can optionally store values separately from keys to generate
a much smaller LSM tree, which results in both lower write
and read amplification.

You asked:

How the data might be structured into files for a graph database

The data is stored into posting lists by subject-predicate groups. Any node that contains a list of edges would store that list in a single posting list until it reaches a certain threshold in size and needs to be split across two or ore lists. This could be understood that any one to one relationship edge or predicate is saved in its own list and one-to-many relationship edge or predicates are grouped together and saves the linking uid or object value into a list. The main take away is that Badger stores values separate from keys to generate a smaller tree which increases read and write performance.

What must happen when querying/updating in terms of loading stuff into memory at a high level.

Manish just released a blog post about memory management and can explain that aspect better than myself.


split string for calculations I'm doing some basic statistical calculations on the results of a survey. A survey consists of several sections and each section has some questions in it. The answers to each question in each section are stored.

The ResultsTable table has Result column which contains computed result for the entire survey as well as overall results for each section, separated by double pipe. For example,

Moderate||Low||High||Low||Low||Low||NA||NA

Above, Moderate is overall survey result, Low is result for section 1, High is for section 2 and so on.

(Then I would be computing some statistics, such as % of people who have High as their result for a specific section. Or, of those who have High for a specific section the % who answered Yes or No to a specific question in that section, etc.)

The problem I'm facing is that I need to split string, which I think could cause performance problems. What I have right now is as follows.

SELECT COUNT(*) [Personal_Health_High_Risk] FROM
    (
        SELECT rt.*
        FROM ResultsTable rt
        WHERE
            rt.CampaignID IN (SELECT Value FROM ufn_ParseDelimitedString(@campaignIDs, ',')) AND
            rt.SectionID = @sectionID
    ) sq
WHERE
    -- where 3rd section result is High
    (SELECT Value FROM ufn_ParseDelimitedString(sq.Result, '||') WHERE RowNumber = 4 ) LIKE '%High%'

The ufn_ParseDelimitedString function splits a string using given delimiter and returns resulting tokens in a table. For example,

-- ufn_ParseDelimitedString output:
--  [RowNumber] [Value]
--  1           Moderate
--  2           Low
--  3           High
--  ...

So, is it better to continue with what I'm doing - basically split the string on each select? OR I was thinking taking that inner SELECT in code above and saving output into temporary table and actually expanding the concatenated result string into separate columns. This way I could reuse temporary table for various other calculations. What are your thoughts on this?

Doing string manipulation in SQL is non-optimal for the engine; it's not meant for that kind of row by row processing.

Your option of using temporary tables to hold intermediary results is going to be your best bet on solving the performance problem.  At least that way it's only split once.  But there are solutions that may work better for you.

Can you modify the import process so the data is split up before it's saved to the database?
What about importing to staging tables and using either an Agent Job or external process to split the data up into permanent tables?

I'm not sure how much reporting you are doing on older data sets; if you only need to report on the data once or twice then temp tables are probably your best bet.  But if you want or need to do longer term analysis then continually splitting the same data is unnecessarily expensive.  Storage is usually cheaper than CPU.


Creating an Index on a varchar(1024) column succeeded. Does it work? When I created an Index on a varchar(1024) column using SSMS GUI, it failed with the message:

"Warning! The maximum key length is 900 bytes. The index 'XXX' has maximum length of 1024 bytes. For some combination of large values, the insert/update operation will fail."

However, the same operation succeeded when I used SQL (not GUI). I'm not sure why this happened, but does this mean the index will work until the data over 900 bytes really is inserted into the column?

(The column currently doesn't have value over 900 bytes.)

environment:
- SQL Sever 2012
- Windows Server 2008 R2 SP1

All versions

If you create an index on a column that its definition is longer than 900 or 1700 (depending on version and type of index), you'll either get a warning and the index creation will succeed (if the table is empty or the existing data are not that long) or an error and the index creation will fail (if a row or more exceed the limit).

For the warning case, the limits are:

SQL Server 2014 and Older

If you create a clustered index on a varchar(>900) column you will receive a warning when creating the index.

If you create a non-clustered index on a varchar(>900) column you will receive a warning message when creating the index.

SQL Server 2016 and Newer

If you create a clustered index on a varchar(>900) column you will receive a warning message when creating the index.

If you create a non-clustered index on a varchar(>1700) column you will receive a warning message when creating the index.

Inserting Data

If you don't insert more data than is limited by the index and the version of SQL Server then you will not receive an error message in your application / SSMS / SQL Query / ...

However, inserting more than the limits specified by the version and index type will result in an error message.

Testing

You can try this out over at db<>fiddle. The links for each test are provided below the test examples.

Varchar Non-Clustered Index Limitations on SQL Server 2012

CREATE TABLE VarcharNonClustered
(
  ID INT
  ,Name VARCHAR(2000)
)

GO

✓

CREATE NONCLUSTERED INDEX idx_VarcharNonClustered_Name ON VarcharNonClustered(Name)
GO

✓

Warning! The maximum key length is 900 bytes. The index 'idx_VarcharNonClustered_Name' has maximum length of 2000 bytes. For some combination of large values, the insert/update operation will fail.

INSERT INTO VarcharNonClustered
VALUES (1,REPLICATE('a',1701))
GO

Msg 1946 Level 16 State 3 Line 1
Operation failed. The index entry of length 1701 bytes for the index 'idx_VarcharNonClustered_Name' exceeds the maximum length of 900 bytes.

INSERT INTO VarcharNonClustered
VALUES (1,REPLICATE('a',1700))
GO

Msg 1946 Level 16 State 3 Line 1
Operation failed. The index entry of length 1700 bytes for the index 'idx_VarcharNonClustered_Name' exceeds the maximum length of 900 bytes.

INSERT INTO VarcharNonClustered
VALUES (1,REPLICATE('a',901))
GO

Msg 1946 Level 16 State 3 Line 1
Operation failed. The index entry of length 901 bytes for the index 'idx_VarcharNonClustered_Name' exceeds the maximum length of 900 bytes.

INSERT INTO VarcharNonClustered
VALUES (1,REPLICATE('a',900))
GO

1 rows affected

db<>fiddle here

Varchar Clustered Index Limitations on SQL Server 2012

CREATE TABLE VarcharClustered
(
  ID INT
  ,Name VARCHAR(2000)
)
GO

✓

CREATE CLUSTERED INDEX idx_VarcharClustered_Name ON VarcharClustered(Name)
GO

✓

Warning! The maximum key length is 900 bytes. The index 'idx_VarcharClustered_Name' has maximum length of 2000 bytes. For some combination of large values, the insert/update operation will fail.

INSERT INTO VarcharClustered
VALUES (1,REPLICATE('a',1701))
GO

Msg 1946 Level 16 State 3 Line 1
Operation failed. The index entry of length 1701 bytes for the index 'idx_VarcharClustered_Name' exceeds the maximum length of 900 bytes.

INSERT INTO VarcharClustered
VALUES (1,REPLICATE('a',1700))
GO

Msg 1946 Level 16 State 3 Line 1
Operation failed. The index entry of length 1700 bytes for the index 'idx_VarcharClustered_Name' exceeds the maximum length of 900 bytes.

INSERT INTO VarcharClustered
VALUES (1,REPLICATE('a',901))
GO

Msg 1946 Level 16 State 3 Line 1
Operation failed. The index entry of length 901 bytes for the index 'idx_VarcharClustered_Name' exceeds the maximum length of 900 bytes.

INSERT INTO VarcharClustered
VALUES (1,REPLICATE('a',900))
GO

1 rows affected

db<>fiddle here

Varchar Non-Clustered Index Limitations on SQL Server 2016

CREATE TABLE VarcharNonClustered
(
  ID INT
  ,Name VARCHAR(2000)
)

GO

✓

CREATE NONCLUSTERED INDEX idx_VarcharNonClustered_Name ON VarcharNonClustered(Name)
GO

✓

Warning! The maximum key length for a nonclustered index is 1700 bytes. The index 'idx_VarcharNonClustered_Name' has maximum length of 2000 bytes. For some combination of large values, the insert/update operation will fail.

INSERT INTO VarcharNonClustered
VALUES (1,REPLICATE('a',1701))
GO

Msg 1946 Level 16 State 3 Line 1
Operation failed. The index entry of length 1701 bytes for the index 'idx_VarcharNonClustered_Name' exceeds the maximum length of 1700 bytes for nonclustered indexes.

INSERT INTO VarcharNonClustered
VALUES (1,REPLICATE('a',1700))
GO

1 rows affected

INSERT INTO VarcharNonClustered
VALUES (1,REPLICATE('a',901))
GO

1 rows affected

INSERT INTO VarcharNonClustered
VALUES (1,REPLICATE('a',900))
GO

1 rows affected

db<>fiddle here

Varchar Clustered Index Limitations on SQL Server 2016

CREATE TABLE VarcharClustered
(
  ID INT
  ,Name VARCHAR(2000)
)
GO

✓

CREATE CLUSTERED INDEX idx_VarcharClustered_Name ON VarcharClustered(Name)
GO

✓

Warning! The maximum key length for a clustered index is 900 bytes. The index 'idx_VarcharClustered_Name' has maximum length of 2000 bytes. For some combination of large values, the insert/update operation will fail.

INSERT INTO VarcharClustered
VALUES (1,REPLICATE('a',1701))
GO

Msg 1946 Level 16 State 3 Line 1
Operation failed. The index entry of length 1701 bytes for the index 'idx_VarcharClustered_Name' exceeds the maximum length of 900 bytes for clustered indexes.

INSERT INTO VarcharClustered
VALUES (1,REPLICATE('a',1700))
GO

Msg 1946 Level 16 State 3 Line 1
Operation failed. The index entry of length 1700 bytes for the index 'idx_VarcharClustered_Name' exceeds the maximum length of 900 bytes for clustered indexes.

INSERT INTO VarcharClustered
VALUES (1,REPLICATE('a',901))
GO

Msg 1946 Level 16 State 3 Line 1
Operation failed. The index entry of length 901 bytes for the index 'idx_VarcharClustered_Name' exceeds the maximum length of 900 bytes for clustered indexes.

INSERT INTO VarcharClustered
VALUES (1,REPLICATE('a',900))
GO

1 rows affected

db<>fiddle here

Summary

SQL Server 2012 + Non-Clustered Index on Varchar() = 900 characters max
SQL Server 2012 + Clustered Index on Varchar() = 900 characters max
SQL Server 2016 + Non-Clustered Index on Varchar() = 1700 characters max
SQL Server 2016 + Clustered Index on Varchar() = 900 characters max

Your Question

  Creating an Index on a varchar(1024) column succeeded. Does it work?

It will work with the above limitations.

References

Maximum Capacity Specifications for SQL Server (SQL Server 2016)
Maximum Capacity Specifications for SQL Server (SQL Server 2012)


Can a stored procedure reference the database in which it is stored? Suppose I have a stored procedure that is duplicated, with some modifications, in several databases.  And I want to reference the database in which the stored procedure is stored, even if it is executed in another database.

Is there a way to retrieve the full path (..) or otherwise retrieve the database in which the stored procedure is stored, rather than the current database?

I want to reference the database in which the stored procedure is stored, even if it is executed [from] another database.

Just use one-part or two-part names in the stored procedure, and it will reference objects in the database containing the stored procedure.  In particular,

For static SQL in a stored procedure:

Unqualified object names will resolve relative to the schema containing the stored procedure.
Two-part names will resolve relative to the database containing the stored procedure.

For dynamic SQL in a stored procedure:

Unqualified object names will resolve relative to the default schema of the user identity running the stored procedure (by default, the caller).
Two-part names will resolve relative to the database containing the stored procedure.

The db_name() function will return the name of the database containing the stored procedure in both cases.


SQL Server stored procedure permissions I know that it's possible to set execute permissions for a stored procedure to a database role or user.  My question is, how is access to any tables that the SP accesses determined?  Does the principal who calls the SP also have to have requisite permissions for the underlying objects that are touched by the SP?

One only needs to ensure that the SP and objects that the SP accesses all have the same owner (ownership chaining rules)--then SQL Server doesn't look at permissions of chained objects.  From the online SQL Server documentation:

  Stored procedures take advantage of ownership chaining to provide
  access to data so that users do not need to have explicit permission
  to access database objects. An ownership chain exists when objects
  that access each other sequentially are owned by the same user. For
  example, a stored procedure can call other stored procedures, or a
  stored procedure can access multiple tables. If all objects in the
  chain of execution have the same owner, then SQL Server only checks
  the EXECUTE permission for the caller, not the caller's permissions on
  other objects. Therefore you need to grant only EXECUTE permissions on
  stored procedures; you can revoke or deny all permissions on the
  underlying tables.


How MySQL Table cache hitrate should be calculated? I have a MySQL 5.7.15 instance which is monitored by nagios with check_mysql_health plugin.

One of the metrics monitored is tablecache-hitrate which is triggering a critical alert. (hitrate ~50%)

My current table_open_cache value is the default of 2K.

My current GLOBAL stats are:

| Max_used_connections                          | 20      |
| Opened_tables                                 | 5278    |
| Open_tables                                   | 2000    |
| Table_open_cache_hits                         | 803395  |
| Table_open_cache_misses                       | 5278    |
| Table_open_cache_overflows                    | 3271    |

The UPTIME at the time of those status were:

Uptime:         20 days 22 hours 30 min 45 sec

My Current tables by storage engine:

+-----------------------+-----------+
|  engine               | count(*)  |
+-----------------------+-----------+
|    InnoDB             |   105     |
|    MEMORY             |   51      |
|    CSV                |   2       |
|    MyISAM             |   11      |
|    PERFORMANCE_SCHEMA |   87      |
|    NULL               |  115      |
+-----------------------+-----------+

That's 256 tables and 115 views. 371 items.

I have a cronjob that runs a whole mysqldump every saturday. I take a status variables on friday and on sunday. The values of last friday and monday are:

Friday:

| Max_used_connections                          | 20        |
| Open_tables                                   | 2000      |
| Opened_tables                                 | 4309      |
| Table_open_cache_hits                         | 513027    |
| Table_open_cache_misses                       | 4309      |

Monday:

| Max_used_connections                          | 20       |
| Open_tables                                   | 2000     |
| Opened_tables                                 | 4924     |
| Table_open_cache_hits                         | 699751   |
| Table_open_cache_misses                       | 4924     |

I've seen a couple of ways to calculate tablecache_hitrate:

Open_tables / Opened_tables
Table cache hit rate = table_open_cache*100/Opened_tables

With those approaches the table_cache_hitrate will lower as the uptime increases, because of new tables created, mysqldump the whole database (opening all tables), etc...

Q1. Does mysqldump bypass table_open_cache? Or it uses already cached tables?

So i think that they aren't a reliable way to calculate tablecache_hitrate.

I guess for MySQL 5.6.6+, it can be calculated based on Table_open_cache_hits and Table_open_cache_misses.

Q2. Is this correct? Or what would be the most accurate way to calculate table_open_cache hitrate?

Regards!

I could be that the plugin is looking at deltas, and the dump had to open all the tables at least once, thereby running through the cache and getting a burst of "misses".

The "hits" are quite high relative to the other values, so I don't see a real 'problem'.  But you could increase table_open_cache and table_open_cache_instances.

What was Max_used_connections?  I guess that number was rather high, and the many connections were opening lots of tables, thereby reaching up to (and past) 2000.  Later, when the dump came along, many of the little-used tables had been bumped out of cache, hence a low hit rate.

Suggest you grab those GLOBAL STATUS readings before and after a dump.  This may (or may not) confirm my guesses.