MDF file size and performance We have peaks where latency (as measured by [Physical Disk\Avg Disk Sec read] on the drive where our data file is) goes up to 0.2 sec.

I immediately started suspecting the SAN while the DBA is suspecting that this is related to the fact that we currently have a single MDF file that is 5 terabytes.

Does a very large MDF file negatively impact latency? If so what is the appropriate size?

I have not seen a lot of content on this subject.

Thanks!
-Xavier

Does a very large MDF file negatively impact latency? If so what is the appropriate size?

Only the size of MDF does not impact IO latency. Latency is related to your disk IO subsystem, its configuration and disk partition alignment.

To find out if disk subsystem is a bottleneck, use sys.dm_io_virtual_file_stats DMV or Capturing IO latencies for a period of time

Having multiple data files on separate LUNs - depends on number of CPU Cores will help to alleviate contention to some extent as well.

Look at the NUMA configuration of your server instance, since there is a single I/O thread and a single lazy writer thread for each NUMA node..

  We have peaks where latency (as measured by [Physical Disk\Avg Disk Sec read] on the drive where our data file is) goes up to 0.2 sec.

You should look into the autogrowth events and if there are frequent autogrowth events fired, you will see a performance hit. Make sure, you have configured autogorowth to a reasonable value - definitely away from Percent increase. Also, configure Instant file initialization, so that data files can leverage its awesomeness.


SSRS 2012 Report with Oracle database as datasource never finishes I am investigating a SSRS 2012 Report which used to return data from an Oracle database. However, recently it stopped returning data. The report will sit there and run forever until something times out. Our report catalog database is also on a separate server which does not show any issues in the logs or while monitoring the performance. I do not see any errors in the Event log, SQL Server Logs, Reporting Services logs or the Oracle database logs. If I strip out the query from the report it returns rows in a few minutes. Is there a method for monitoring what is happening with the communication between the report server and the oracle server? How can I troubleshoot this issue if none of the logs are reporting any problems?

Looks like I am now getting Timeout errors in the report logs.

System.Data.SqlClient.SqlException: Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.

When I run the query against the Oracle database using SQL Plus, it takes about 8 minutes to run.

I would recommend the following:

1) Run the report and check Oracle's session (v$session) view to see if the Oracle database even registered the connection and the query was passed to it.

a) if YES, then check your report server's timeout settings. If the initial data return is elapsing the timeout period in either your report or your RS config file, this may likely be the reason for your timeout issue. You can check out report timeout periods in your data source configurations. You can check time out RS settings in your report server's config file.

b) if NO, you've got something else going that is more fundamental in connecting to the Oracle db and submitting the query.


SQL Server 2012 Managment Studio not saving auto recovery files I tried to go to Tools > Environment > AutoRecover.

The check box of save autorecover is checked.
Save every "5" minutes.
Keep autorecover info for: 7 days.

But it doesn't save any auto recover files (I searched inside my user folder for *.sql), and doesn't suggest to auto recover when the Management Studio closes by a power down.

I searched the registry to see if something was changed manually in HKEY_CURRENT_USER\Software\Microsoft\Microsoft SQL Server but only found one key: CustomerFeed.

According to AutoRecover information is not saved, SSMS 2012 only saves recovery files for queries that have been previously saved (which has led to some complaints). Files that have not been saved seem to get dumped in C:\Users\%USERNAME%\AppData\Local\Temp.

So you can check:

C:\Users\%USERNAME%\Documents\SQL Server Management Studio
C:\Users\%USERNAME%\AppData\Local\Temp

Personally I use TFS for version control on my queries and save them before every run. I've lost a fair number of queries in SSMS so I tend to be very careful.

If you can't find them on disk, there is a way to recover queries from the procedure cache as long as you catch them before they get pushed out of the cache. See also Execution Plan Caching and Reuse.


Split duration hourly depending on start and end time I have a table that registers the status for equipment. The status has a start and an end time. But now I want to know for every hour what the status is. If the status was "Operating" from 15:20 to 17:10, I want to see that it was 40 minutes on Operating for the 16th hour of the day, 60 minutes operating on the 17th hour and 10 minutes operating on the 18th hour of the day.

This is what I have now:

Shift_date  Status     Start_timestamp  End_Timestamp
----------  ---------  ---------------  ---------------
5/20/2017   Operating  5/20/2017 8:21   5/20/2017 10:40
5/21/2017   Delay      5/20/2017 10:40  5/20/2017 11:10
5/22/2017   Operating  5/20/2017 11:10  5/20/2017 13:50

This is what I want:

Shift_date  Hour  Status     Duration (minutes)
----------  ----  ---------  ------------------
5/20/2017   1     ..         ..
5/20/2017   ..    ..         ..
5/20/2017   9     Operating  39
5/20/2017   10    Operating  60
5/20/2017   11    Operating  40
5/20/2017   11    Delay      20
5/20/2017   12    Delay      10
5/20/2017   12    Operating  50
5/20/2017   13    Operating  50
5/20/2017   13    ..         ..
5/20/2017   14    ..         ..
5/20/2017   ..    ..         ..
5/20/2017   24    ..         ..

The basic idea here is to add rows for each row in the status table depending on how many hours it covers. I'm doing this by joining to a numbers table. From there, you just need to account for all of the different cases to figure out how many minutes for the status are relevant for each hour bucket.

Data prep:

CREATE TABLE #t (
Shift_date DATETIME NOT NULL,
[Status] VARCHAR(20) NOT NULL,
Start_timestamp DATETIME NOT NULL,
End_Timestamp DATETIME NOT NULL
);

INSERT INTO #t VALUES ('5/20/2017', 'Operating', '5/20/2017 8:21', '5/20/2017 10:40');
INSERT INTO #t VALUES ('5/20/2017', 'Delay', '5/20/2017 10:40', '5/20/2017 11:10');
INSERT INTO #t VALUES ('5/20/2017', 'Operating', '5/20/2017 11:10', '5/20/2017 13:50');

One approach which should work on 2008:

-- may need more rows here depending on how long the events can be
WITH numbers (n) AS (
    SELECT 0
    UNION ALL SELECT 1
    UNION ALL SELECT 2
    UNION ALL SELECT 3
    UNION ALL SELECT 4
    UNION ALL SELECT 5
)
SELECT
   t2.Shift_date
 , DATEPART(HOUR, start_final) AS [hour]
 , t2.[Status]
 , DATEDIFF(MINUTE, start_final, end_final) Duration
FROM
(
    SELECT Shift_date
    , [Status]
    , Start_timestamp
    , End_Timestamp
    , CASE WHEN t.Start_timestamp > n.start_from_numbers THEN t.Start_timestamp ELSE n.start_from_numbers END start_final
    , CASE WHEN t.End_timestamp > n.end_from_numbers THEN n.end_from_numbers ELSE t.End_timestamp END end_final
    FROM #t t
    CROSS APPLY
    (
        SELECT dateadd(hour, n.n + datediff(hour, 0, t.Start_timestamp), 0) start_from_numbers
        , dateadd(hour, 1 + n.n + datediff(hour, 0, t.Start_timestamp), 0) end_from_numbers
        FROM numbers n WHERE DATEDIFF(HOUR, t.Start_timestamp, t.End_Timestamp) >= n.n
    ) n
) t2
ORDER BY start_final;

My results (it looks like you have a few issues with your sample data):

╔═════════════════════════╦══════╦═══════════╦══════════╗
║       Shift_date        ║ hour ║  Status   ║ Duration ║
╠═════════════════════════╬══════╬═══════════╬══════════╣
║ 2017-05-20 00:00:00.000 ║    8 ║ Operating ║       39 ║
║ 2017-05-20 00:00:00.000 ║    9 ║ Operating ║       60 ║
║ 2017-05-20 00:00:00.000 ║   10 ║ Operating ║       40 ║
║ 2017-05-20 00:00:00.000 ║   10 ║ Delay     ║       20 ║
║ 2017-05-20 00:00:00.000 ║   11 ║ Delay     ║       10 ║
║ 2017-05-20 00:00:00.000 ║   11 ║ Operating ║       50 ║
║ 2017-05-20 00:00:00.000 ║   12 ║ Operating ║       60 ║
║ 2017-05-20 00:00:00.000 ║   13 ║ Operating ║       50 ║
╚═════════════════════════╩══════╩═══════════╩══════════╝


What units used in DBA_TABLESPACE_USAGE_METRICS? What units are used in the usage metrics (USED_SPACE and TABLESPACE_SIZE columns) described in Oracle's DBA_TABLESPACE_USAGE_METRICS static data dictionary view?

The Oracle Database Reference only tells they are the "Total space consumed by the tablespace" and "Total size of the tablespace", without mentioning units. Are they in bytes? Megabytes? Blocks?

Blocks. Blocks are the units of the DBA_TABLESPACE_USAGE_METRICS.USED_SPACE and DBA_TABLESPACE_USAGE_METRICS.TABLESPACE_SIZE. The latter column accounts for possible AUTOEXTEND MAXSIZE.

EDIT: I'm not sure what is the meaning of USED_SPACE for undo tablespace though. For example:

SQL> SELECT tablespace_name, sum(blocks), status FROM dba_undo_extents GROUP BY tablespace_name, status ;

TABLESPACE_NAME                SUM(BLOCKS) STATUS
------------------------------ ----------- ---------
UNDOTBS1                               128 ACTIVE
UNDOTBS1                              5312 UNEXPIRED
UNDOTBS1                              8960 EXPIRED

SQL> select USED_SPACE from DBA_TABLESPACE_USAGE_METRICS WHERE TABLESPACE_NAME='UNDOTBS1';

USED_SPACE
----------
      2864

SQL> show parameter block_size

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_block_size                        integer     8192


Is MySQL replication suitable for a Write Mostly application? I'm considering adding another server to my one-server setup application with 600GB MySQL DB. The db and the apache are both running from the same server. In order to avoid backup downtime and improve performance, I'm considering to use the additional server as a replication of the db hoping that the write will happen to the master and all the reads will happen from the slave, make it work in parallel.

Is this suitable for an application with many writes? (70%)
I assume the write are more effective when done in bulks to the slave. Can I control this? How more effective are they?
Is there an easy option to do 'down-time' on one of the servers (e.g. backup) and return to the same system afterwards?

MySQL Replication would probably victimize you in terms of Replication Lag.
If you run SHOW SLAVE STATUS\G on a Slave whose Master is write-heavy, the data you need to read may not be ready to read on the Slave until the Replication Lag (Seconds_Behind_Master) is 0.
SUGGESTION
If you want reads available on a Slave as well as on a Master, consider using Percona XtraDB Cluster. It does synchronized replication so that the same data exists at any point in time on both Master and Slave. It does not require binary logging or MySQL Replication. In addition, you can use a LoadBalanced DBVIP against Master and Slave to distribute reads.
The only drawback is that this might produce slower writes.
Provided all data is InnoDB, mysqldump backups can be done against the Slave using --single-transaction.
Here are my past posts on Percona XtraDB Cluster

Aug 20, 2012 : Best solution for cross-datacenter MySQL master-slave replication
Jul 25, 2012 : Scaling Percona datacenters: setup and replication
Aug 26, 2011 : MySQL Master/Slave Master/Master setup?


Inserting two tables together with no Id or UNIQUE anything Let's assume we have two tables:

table1, retaining the following data:

Name Height Age
---- ------ ---
Mary   1.60  18
Mary   1.62  19
Mary   1.68  20
John   1.80  17
John   1.85  18
John   1.86  19

table2, with the rows shown below:

Name Height Age
---- ------ ---
Mary   1.60  18
Mary   1.62  19
Mary   1.68  20
John   1.80  17
John   1.85  18
John   1.86  19
Vince  1.78  22
Vince  1.80  23

How would I JOIN these two tables using Sybase so that afterward table1 has Vinces data in it?

Think of table2 as a temporary table that contains new information. I want to update table1 with the new information.

Should be:

insert into table1
select * from table2
except
select * from table1;

You could also use:

select *
from table2
where Name not in (select Name from table1)

This works too (assuming Name cannot be NULL).

The difference is that the except version delivers all rows that are not the same whereas the not in query delivers all rows where the name does not exist. Up to you to decide if this is what you need.


Why PostGIS GIST index is not used? I am using PostGIS 2.2.1 with PostgreSQL 9.5.10. I have a table with a geography field for coordinates and I am making a simple bounding box query. The table has 1029 rows. EXPLAIN says it does a sequential scan. What is going on?

masa=# \d+ store
                                                          Table "public.store"
  Column   |          Type          |                      Modifiers                      | Storage  | Stats target |    Description
-----------+------------------------+-----------------------------------------------------+----------+--------------+--------------------
 id        | integer                | not null default nextval('store_id_seq1'::regclass) | plain    |              |
 parent_id | integer                |                                                     | plain    |              |
 name      | character varying(255) | not null                                            | extended |              |
 coords    | geography(Point,4326)  |                                                     | main     |              | (DC2Type:geopoint)
Indexes:
    "store_pkey" PRIMARY KEY, btree (id)
    "uniq_ff5758775e237e06" UNIQUE, btree (name)
    "idx_ff575877727aca70" btree (parent_id)
    "idx_ff575877d86de985" gist (coords)
Foreign-key constraints:
    "fk_ff575877727aca70" FOREIGN KEY (parent_id) REFERENCES store(id)
Referenced by:
    TABLE "store_product" CONSTRAINT "fk_ca42254ab092a811" FOREIGN KEY (store_id) REFERENCES store(id) ON DELETE CASCADE
    TABLE "store" CONSTRAINT "fk_ff575877727aca70" FOREIGN KEY (parent_id) REFERENCES store(id)

masa=# explain SELECT * FROM store WHERE coords && ST_MakeEnvelope(24.93522187393,60.155284015594,24.95327552607,60.164258984406, 4326);
                                                                                QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Seq Scan on store  (cost=0.00..27.86 rows=206 width=58)
   Filter: (coords && '0103000020E610000001000000050000007F0863B36AEF3840AD48BC58E0134E407F0863B36AEF3840890C3B7006154E40408C68DD09F43840890C3B7006154E40408C68DD09F43840AD48BC58E0134E407F0863B36AEF3840AD48BC58E0134E40'::geography)
(2 rows)

masa=#

Apparently the reason was that table needed vacuum/analyze

masa=# VACUUM FULL ANALYZE store;
VACUUM
masa=# explain SELECT * FROM store WHERE coords && ST_MakeEnvelope(24.93522187393,60.155284015594,24.95327552607,60.164258984406, 4326);
                                                                                QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Index Scan using idx_ff575877d86de985 on store  (cost=0.14..8.16 rows=1 width=82)
   Index Cond: (coords && '0103000020E610000001000000050000007F0863B36AEF3840AD48BC58E0134E407F0863B36AEF3840890C3B7006154E40408C68DD09F43840890C3B7006154E40408C68DD09F43840AD48BC58E0134E407F0863B36AEF3840AD48BC58E0134E40'::geography)
(2 rows)

masa=#

Thanks to Evan Carrol I figured I simply needed to run # VACUUM FULL ANALYZE store; on my table (which was named store) and then database started using the index on queries.


Cumulative Sum by distinct contact_ID by year-month I've seen some posts here regarding the SUM OVER function to calculate the cumulative sum. But the issue which I have is slightly different. I need to keep a running total of distinct inactive clients by year-month. So far I've created the query to give the result as:

date       InactiveClients  TotalInactiveClients
2016-04    91954             91954
2016-03    90065             182019 <-- sums 2016-04 and 2016-03
2016-02    84517             266536 <-- but in past 3 months i had around 80k of inactivity
2016-01    101561            368097
2015-12    113953            482050
2015-11    85425             567475
2015-10    95681             663156
2015-09    80503             743659
2015-08    82327             825986
2015-07    96672             922658
2015-06    84375             1007033
2015-05    83740             1090773
2015-04    96933             1187706
2015-03    68572             1256278

The query is which gives me the result is here:

SELECT
      Date_FORMAT(pq.date, "%Y-%m") date,
      pq.InactiveClients as 'InactiveClients',
      @runBal := @runBal + pq.InactiveClients as TotalInactiveClients
  FROM ( SELECT
           distinct ss1.time date,
           COUNT(DISTINCT c1.id) as InactiveClients
         FROM `contacts` c1, `statengineSent` ss1
           LEFT JOIN `statengineViews` AS sv1
             ON (sv1.email = ss1.email
             AND sv1.dispatchID = ss1.dispatchID
             AND sv1.email is NULL)
         WHERE ss1.email = c1.email
           AND ss1.time > DATE_SUB(now(), interval 13 MONTH)
         GROUP BY YEAR(date), MONTH(date)
         ORDER BY date desc ) pq,
      ( select @runBal := 0 ) sqlvars

PS: in case you are wondering with the following LEFT JOIN I am excluding all the active contacts.

LEFT JOIN `statengineViews` AS sv1
  ON (sv1.email = ss1.email
  AND sv1.dispatchID = ss1.dispatchID
  AND sv1.email is NULL)
WHERE ss1.email = c1.email
  AND ss1.time > DATE_SUB(now(), interval 13 MONTH)

I need help with the following:
Could anyone help me to make the result of TotalInactiveClients equal the sum of previous months unique contact IDs?
(For example 2015-03 TotalInactiveClients = uniqe count of contactIDs inbetween 2015-03 upto 2016-04. So for 2015-10 - i need all the contactID's which occurred from 2016-04 to 2015-10 (including 2015-10)).

OK since no-one seemed to reply (except for Michael Green - Thanks) i did the MySQL statement the long way. If anyone has an idea how to make it smaller or how to combine them, then please let me know:

Last months inactivity:

Select b.date , count(b.contactID) FROM
(SELECT distinct Date_FORMAT(ss1.time, "%Y-%m") as date, c1.id as ContactID
           FROM `contacts` c1, `statengineSent` ss1
           LEFT JOIN `statengineViews` AS sv1
           ON (sv1.email = ss1.email  AND sv1.dispatchID = ss1.dispatchID AND sv1.email is NULL)
           WHERE ss1.email = c1.email
           AND YEAR(ss1.time) = YEAR(CURRENT_DATE - INTERVAL 0 MONTH)
                   AND MONTH(ss1.time) = MONTH(CURRENT_DATE - INTERVAL 0 MONTH)
                   ORDER BY date desc)a,
(SELECT distinct Date_FORMAT(ss1.time, "%Y-%m") as date, c1.id as ContactID
           FROM `contacts` c1, `statengineSent` ss1
           LEFT JOIN `statengineViews` AS sv1
           ON (sv1.email = ss1.email  AND sv1.dispatchID = ss1.dispatchID AND sv1.email is NULL)
           WHERE ss1.email = c1.email
           AND YEAR(ss1.time) = YEAR(CURRENT_DATE - INTERVAL 1 MONTH)
                   AND MONTH(ss1.time) = MONTH(CURRENT_DATE - INTERVAL 1 MONTH)
                   ORDER BY date desc)b
WHERE a.ContactID = b.ContactID

The result was:

date    inactivity
2016-03 83108

2 Months ago inactivity:

Select c.date , count(c.contactID) FROM
(SELECT distinct Date_FORMAT(ss1.time, "%Y-%m") as date, c1.id as ContactID
           FROM `contacts` c1, `statengineSent` ss1
           LEFT JOIN `statengineViews` AS sv1
           ON (sv1.email = ss1.email  AND sv1.dispatchID = ss1.dispatchID AND sv1.email is NULL)
           WHERE ss1.email = c1.email
           AND YEAR(ss1.time) = YEAR(CURRENT_DATE - INTERVAL 0 MONTH)
                   AND MONTH(ss1.time) = MONTH(CURRENT_DATE - INTERVAL 0 MONTH)
                   ORDER BY date desc)a,
(SELECT distinct Date_FORMAT(ss1.time, "%Y-%m") as date, c1.id as ContactID
           FROM `contacts` c1, `statengineSent` ss1
           LEFT JOIN `statengineViews` AS sv1
           ON (sv1.email = ss1.email  AND sv1.dispatchID = ss1.dispatchID AND sv1.email is NULL)
           WHERE ss1.email = c1.email
           AND YEAR(ss1.time) = YEAR(CURRENT_DATE - INTERVAL 1 MONTH)
                   AND MONTH(ss1.time) = MONTH(CURRENT_DATE - INTERVAL 1 MONTH)
                   ORDER BY date desc)b,
(SELECT distinct Date_FORMAT(ss1.time, "%Y-%m") as date, c1.id as ContactID
           FROM `contacts` c1, `statengineSent` ss1
           LEFT JOIN `statengineViews` AS sv1
           ON (sv1.email = ss1.email  AND sv1.dispatchID = ss1.dispatchID AND sv1.email is NULL)
           WHERE ss1.email = c1.email
           AND YEAR(ss1.time) = YEAR(CURRENT_DATE - INTERVAL 2 MONTH)
                   AND MONTH(ss1.time) = MONTH(CURRENT_DATE - INTERVAL 2 MONTH)
                   ORDER BY date desc)c
WHERE a.ContactID = b.ContactID AND b.ContactID = c.ContactID

The result was:

date    inactivity
2016-02 82355

AS you can see right now i'm using 2 statements which already are very big and sloppy and also i need to run 12 different queries to get the whole year inactivity. Can anyone help me combine these or make it work as an interval for a whole year?
It does the trick but as i said, it's very sloppy :(


how to insert big data in mariadb with execution time less than max_statement_time value i have some table with this structure.
CREATE TABLE `trcdmppdetail` (
  `mpp_code` varchar(50) NOT NULL,
  `company_code` varchar(50) NOT NULL,
  `mppdetail_date` datetime(3) NOT NULL,
  `position_id` int(11) DEFAULT NULL,
  `basedon_code_1` varchar(50) NOT NULL,
  `basedon_code_2` varchar(50) NOT NULL,
  `mpp_emp` int(11) DEFAULT NULL,
  `actualemp_onrequest` int(11) DEFAULT NULL,
  `dept_id` int(11) NOT NULL,
  `created_date` datetime(3) DEFAULT NULL,
  `created_by` varchar(50) DEFAULT NULL,
  `modified_date` datetime(3) DEFAULT NULL,
  `modified_by` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`mpp_code`,`company_code`,`mppdetail_date`,`basedon_code_1`,`basedon_code_2`,`dept_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 `ENCRYPTED`=YES `ENCRYPTION_KEY_ID`=3488

and value on global variable max_statement_time=60
i insert 50k row data on thats table but i got error because my query run more than max_statement_time
my dba can't allow me to change global variable max_statement_time=0 becuase he says will be distruption the server.
so how to i insert the data with execution time less than max_statement_time value.

Don't change the GLOBAL setting; change the SESSION setting.


Compare Timestamp Difference Between Consecutive Records How do I compare Timestamp difference between 2 consecutive records?

This is my example:

DECLARE @x TABLE(ProductID INT, SingleRunFailure bit, SampleDate date);
INSERT @x VALUES
(101, 1, '2016-01-01 10:09:19.617'),
(101, 0, '2016-01-05 11:09:19.617'),
(101, 1, '2016-01-07 12:09:19.617'),
(101, 0, '2016-01-09 12:09:19.617'),
(101, 1, '2016-01-15 13:09:19.617'),
(101, 0, '2016-01-17 13:09:19.617'),
(101, 0, '2016-01-29 14:09:19.617'),
(101, 0, '2016-02-02 15:09:19.617');

;WITH cte(ProductID, CCVPassed)
AS
(
   SELECT ProductID, CASE WHEN SUM(LastTwoRunsPass) >= 2 THEN 1 ELSE 0 END
   FROM (SELECT ProductID, LastTwoRunsPass = CASE WHEN SingleRunFailure = 0 THEN 1 ELSE 0 END,
             RowNumber = ROW_NUMBER() OVER(PARTITION BY ProductID ORDER BY SampleDate DESC)
        FROM @x) AS aggregation
   WHERE RowNumber <= 2
   GROUP BY ProductID
)

SELECT * FROM cte;

This returns me a Pass or Fail where SingleRunFailure flag was false on last 2 records.  This part works as expected.

I need to add logic where if the SampleDate difference on the last 2 records was less than 15 minutes return True.

Expected response:

ProductID    CCVPassed
101          0

This is because the difference between last 2 records is 4 days.  If the SampleDate difference between last 2 records was less than 15 minutes CCVPassed would return 1.

This works like this:

It gets numbers for each row of each ProductID ordered by SampleDate (ROW_NUMBER()).
It only keeps first 2 values (biggest SampleDate which are the last 2 rows of the sample)
It calculates the difference in minutes between the 2 SampleDate

Query:

WITH cte AS (
    SELECT ProductID, SampleDate
        , n = ROW_NUMBER() OVER(PARTITION BY ProductId ORDER BY SampleDate DESC)
    FROM @x
)
SELECT ProductID
    , CCVPassed = CASE WHEN DATEDIFF(minute, MIN(SampleDate), MAX(SampleDate)) < 15 THEN 'true' ELSE 'false' END
FROM cte
WHERE n <= 2
GROUP BY ProductID

SQL Fiddle

Output:

ProductID   CCVPassed
101 4       false

Note:

It partitions by ProductId because it supposed you have several ProductId in your real table.
Replace true/false by 1/0 or whatever is needed in the real query


how exactly code.org lost data with 32-bit index? Suppose they use MySQL database Today the headline The 32-Bit Dog Ate 16 Million Kids' CS Homework. Quote from blog.code.org

  The way we store student coding activity is in a table that until
  today had a 32-bit index. What this means is that the database table
  could only store 4 billion rows of coding activity information. We
  didn’t realize we were running up to the limit, and the table got
  full. We have now made a new student activity table that is storing
  progress by students. With the new table, we are switching to a 64-bit
  index which will hold up to 18 quintillion rows of information. On the
  plus side, this new table will be able to store student coding
  information for millions of years. On the down side, until we’ve moved
  everything over to the new table, some students’ code from before
  today may temporarily not appear, so please be patient with us as we
  fix it.

We now suppose the use MariaDB/MySQL. Can you explain me in detail (with SQL statements and error messages) this issue? I don't understand this data loss very well.

By index, he actually means key column. The maximum value MySQL can store in a signed INT column is 2147483647 (documentation link), which is stored in 32 bits (4 bytes, 2 to the power of 32).

Their problem would have occurred when the table reached 2147483647 rows (and less if there are gaps) - any subsequent INSERTs would fail, and the data would be lost.

It's easy to reproduce what's happened:

Create a test table with a INT primary key:

mysql> create table incfail
       (
          pk INT PRIMARY KEY AUTO_INCREMENT
       );
Query OK, 0 rows affected (0.01 sec)

Set the primary key value to be nearly the maximum value:

mysql> alter table incfail AUTO_INCREMENT= 2147483646;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

INSERT a few values:

mysql> insert into incfail values (NULL);
Query OK, 1 row affected (0.00 sec)

mysql> insert into incfail values (NULL);
Query OK, 1 row affected (0.00 sec)

mysql> select * from incfail;
+------------+
| pk         |
+------------+
| 2147483646 |
| 2147483647 |
+------------+
2 rows in set (0.01 sec)

mysql>

The primary key is now at its maximum value, and the next INSERT will fail:

mysql> insert into incfail values (NULL);
ERROR 1062 (23000): Duplicate entry '2147483647' for key 'PRIMARY'
mysql>

The data was lost:

mysql> select * from incfail;
+------------+
| pk         |
+------------+
| 2147483646 |
| 2147483647 |
+------------+
2 rows in set (0.01 sec)

mysql>


How to both reference and return a result set from a stored procedure in mysql? I have read that a select query that stores a result into variables does not return a result set. Is there an efficient way to do both?
BEGIN
   #return this and also use it in the following query
   select id, name, table2_id
   from table1
   where id = variable_id;

   select id, table2_name
   from table2
   where table2.id = table2.id from the previous result set
END

Here you can use user defined variables, as long as you only have only one table2.id
BEGIN
   #return this and also use it in the following query
   select id, name, @id :=  table2_id
   from table1
   where id = variable_id;

   select id, table2_name
   from table2
   where table2.id = @id
END

Or if you have more than one table2.id
BEGIN
   #return this and also use it in the following query
   select id, name, table2.id
   from table1
   where id = variable_id;

   select id, table2_name
   from table2
   where table2.id in (select table2<_id
                       from table1
                       where id = variable_id);
END

Or you can join directly, and ave only one result set
BEGIN
   #return this and also use it in the following query
   select t1.id, t1.name, t1.table2_id,t2,id, t2.table2_name
   from table1 t1 INNER JOIN table2 t2 ON t2.id = t1.table2_id
   where t1.id = variable_id;
END


Two mysql db's load the same data at two different rates Any idea why this might be happening?  We have the same mysql (*.sql file) data being imported into two virtually identical mysql databases every day, but one consistently takes 30% longer to load than the other.

We're using the RDS system on Amazon's AWS.  We have a "staging" db and a "production" db.  The staging db is the one that loads slowly.  We are loading the data through a mysql client that sits on an EC2 Container.  The staging container is virtually identical to the production container.  The staging db is virtually identical to the production db. The staging RDS instance is virtually identical to the production RDS instance.

In the past, the staging db loaded at the same speed as the production db.  At one point, we did some experiments with staging on a different RDS instance.  When those didn't work out, we went back to our previous staging RDS instance and began getting consistently slower import times.

The sql file that gets loaded is a large one and takes almost 30 minutes to load on the production db and 40 minutes on staging.

I can see in the Monitoring charts that the write operations per second on production are a lot higher ... spiking to over 600 vs. only spiking to 240.

EDIT (more info)

These are where they are exactly the same ...

Engine: MySQL 5.1.73b
Option Group: default: mysql-5-1
Parameter Group: barracuda-mysql51
Availability Zone: us-east-1b
Instance class: db.m1.small
Storage Type: General Purpose (SSD)
IOPS: disabled
Storage: 20 GB
Multi AZ: No

They are in different VPC's and different security groups.  Also, the production EC2 container that does the upload is in us-east-1b but the staging one is in us-east-1a.  I'll have to check if that could make a difference.

It looks like it must have been the zone (sub-region) that was the issue.  The slow loading happened when the EC2 container with the *.sql file was in us-east-1a and the RDS instance was in us-east-1b.


Is using indexed view with no expand on standard edition a must? I have recently read the text below in 70-762 Developing SQL Databases (p. 67):

  The first benefit of using an indexed view is that when you use it
  Enterprise Edition of SQL Server, it uses the stored data in the index
  structure. For Standard Edition, it uses the code of the query unless
  you use a NOEXPAND table hint, in which case it uses the clustered
  index representation.

and since we have migrated from Enterprise to Standard (using SQL Server 2016 SP1) and have indexed view, I decide to check how this is affecting the performance. So, I executed the queries below:

DBCC DROPCLEANBUFFERS;

SET STATISTICS IO ON;

SELECT *
FROM [dbo].[vw_my_view]
WITH (NOEXPAND);

and:

DBCC DROPCLEANBUFFERS;

SET STATISTICS IO ON;

SELECT *
FROM [dbo].[vw_my_view];

and the second query was slower and the view definition was expanded, while in the first case, the clustered index scan was used. In this article is said, that NOEXPAND can lead to better performance even on Enterprise Edition.

I am wondering, having the quote above, how indexed view is handled in Enterprise Edition, can we conclude that we should always used WITH (NOEXAPND) in Standard Edition to get the similar behavior?

Why the behavior is not the same in all editions as we have free workaround?

Why the behavior is not the same in all editions as we have free
  workaround?

You have no workaround in case when the poorly performing query does not use any wiev at all.

You can have a situation when some application does some aggregations and with the large volumes of data it became slowly. In case of Enterprise Edition you can create an indexed view that query optimizer could be able to use for this query even if a query does not access any view at all. And you cannot use this approach in other editions as you have no control on the application source code.

This case is described in Paul White's article that you mentioned about:

  Enterprise Edition and View Matching

  On an Enterprise Edition instance, the query optimizer may be able
  to use an indexed view even if the query does not mention the view
  explicitly. If the optimizer is able to match part of the query tree
  to an indexed view, it can choose to do so, based on its estimation
  of the costs of using the view or not. The view-matching logic is
  reasonably clever, but it does have limits that are pretty easy to hit
  in practice. Even where view matching is successful, the optimizer can
  still be misled by inaccurate cost estimations.


How to convert a table into second normal form I am really struggling to convert the table into second normal form.
I've tried examples which are much easier, however I can't seem to be able to identify the keys for this one and work my way forward. I have converted that to 1st normal form by duplicating the module information.

1NF: Only the key
    Remove repeated data/data groups
    i.e. having a person and address table instead of single mega-table
2NF: The whole key
    Ensure that all nonkey columns are dependent on the whole key
    So with a table where employee name and location could be a primary key,
    and there is a skill attribute the skill attribute isn’t dependent
    on the whole of the key as
    it isn’t dependent on location therefore must be split into two tables
3NF And nothing but the key
    Should not have any dependencies on things other than the primary key

Now this seems like a bit of a homework question so I won't do it for you but make a module table, make a room table. If you want third normal form making a booking table with module code and room number as a compound primary key, and the fields relevant to a booking.

Remember normalisation is to make all the data related and remove repetition.


Moving tables to another SQL2008 database (including indexes, triggers, etc.) I need to move a whole bunch (100+) of large (millions of rows) tables from one SQL2008 database to another.

I originally just used the Import/Export Wizard, but all the destination tables were missing primary and foreign keys, indexes, constraints, triggers, etc. (Identity columns were also converted to plain INTs, but I think I just missed a checkbox in the wizard.)

What's the right way to do this?

If this were just a couple of tables, I would go back to the source, script out the table definition (with all indexes, etc), then run the index creation portions of the script on the destination. But with so many tables, this seems impractical.

If there wasn't quite so much data, I could use the "Create Scripts..." wizard to script out the source, including data, but a 72m row script just doesn't seem like a good idea!

Scripting out the tables, then using SSIS to transfer the data would be the most reliable and effective way of moving the data to the new database.


How can I insert records in a table that are dependent on other tables? I have 3 tables:

table1:
Employee_class(PK),
Executive_lounge,
Executive_washroom,
Supply_closet,
Employee_lounge,
Server_room,
Server_closet

table2:
Emp_id(PK),
LName,
Years_of_service,
Emp_Class (FK) ref Employee_class in table1

table3:
Transaction_id (PK),
Emp_id (FK) ref Emp_id in table2,
Access_time,
Access_location,
Allowed

table1 is used to know if a certain class employee can access a particular location

table2 contains details of employees

table3 records every time an employee accesses a particular location. Now, access should be permitted only when the employee class of the employee is allowed to access that particular location.

So what I am trying to do is to insert into table3, but I wish to give constraints as well, i.e., I want to check Emp_Class of Emp_id from table2 and then further check from table1 if Employee_class has access to that particular location, only then allow insert in table3.

How can I do this? I was thinking about writing an insert query with a join on table3 and table2, with case but that is apparently not allowed in SQL. I am free to consider procedures and triggers but as I am new to advanced SQL, I am not sure how to do that.

Thanks. Also, I apologize if my question is framed poorly, I am new to this , please bear.

Since the LOCATIONS and employee CLASSES are quite important for your database, you should probably give them separate tables.  Here some suggestions - which may serve as a starting point (including some test data, using Oracle 12c).

create table locations ( id, description )
as
select 100, 'Executive Lounge' from dual union all
select 200, 'Executive Washroom' from dual union all
select 300, 'Supply Closet' from dual union all
select 400, 'Employee Lounge' from dual union all
select 500, 'Server Room' from dual union all
select 600, 'Server Closet' from dual;

create table employeeclasses ( id, description )
as
select 1, 'class 1' from dual union all
select 2, 'class 2' from dual union all
select 3, 'class 3' from dual union all
select 4, 'class 4' from dual union all
select 5, 'class 5' from dual union all
select 6, 'class 6' from dual;

create table employees ( id, fname, lname, employeeclass )
as
select 5000, 'fname5000', 'lname5000', 1 from dual union
select 5001, 'fname5001', 'lname5000', 2 from dual union
select 5002, 'fname5002', 'lname5000', 1 from dual union
select 5003, 'fname5003', 'lname5000', 2 from dual union
select 5004, 'fname5004', 'lname5000', 6 from dual ;

alter table locations
add constraint pkey_locations primary key ( id ) ;

alter table employeeclasses
add constraint pkey_eclasses primary key ( id );

alter table employees
add constraint pkey_employees primary key ( id ) ;

alter table employees
add constraint fkey_empclass
foreign key ( employeeclass ) references employeeclasses ( id ) ;

When it comes to permissions, things get a bit more complicated :-).  Apparently, you want to be able to assign access permissions to GROUPS (classes of employees).  However, in the "access log" (your table3), you are recording events that are associated with SINGLE users.  Thus, (just as an idea) we could create a PERMISSIONS table, where we record all permissions (per employee).  You see that any of the valid combinations of EMPLOYEEID and LOCATIONID can be recorded in the access log table (code below), whose foreign key constraint references the permissions table.

create table permissions (
  employeeid number references employees( id )
, locationid number references locations( id )
, constraint pkey_permissions
  primary key ( employeeid, locationid )
);

create table accesslog (
  id number generated always as identity start with 2000
, employeeid number
, locationid number
, access_time date default sysdate
, constraint fkey_accesslog
  foreign key ( employeeid, locationid )
  references permissions ( employeeid, locationid )
);

Testing

-- Grant all members of class 1 access to locations 100 and 200.
insert into permissions ( employeeid, locationid )
select id, 100
from employees
where employeeclass in ( 1, 2 )
union
select id, 200
from employees
where employeeclass in ( 1, 2 ) ;

-- Employee 5000 accesses location 100 -> access granted, row inserted.
insert into accesslog ( employeeid, locationid )
values ( 5000, 100 ) ;
-- 1 row inserted.

-- Is employee 5000 allowed access to location 500? No.
insert into accesslog ( employeeid, locationid )
values ( 5000, 500 ) ;
ORA-02291: integrity constraint (...FKEY_ACCESSLOG) violated - parent key not found

-- Employees 5000, 5001, 5002, and 5003 want to access location 200.
-- They have permission to do so.
declare
  location constant number := 200 ;
begin
  for i in 5000 .. 5003
  loop
    insert into accesslog ( employeeid, locationid )
    values ( i, location ) ;
  end loop;
end;
/
-- PL/SQL procedure successfully completed.

Our "access log" now contains:

select id, employeeid, locationid
, to_char( access_time, 'YYYY-MM-DD HH24:MI:SS' )
from accesslog ;

        ID EMPLOYEEID LOCATIONID TO_CHAR(ACCESS_TIME
---------- ---------- ---------- -------------------
      2000       5000        100 2018-10-08 04:46:35
      2002       5000        200 2018-10-08 04:50:20
      2003       5001        200 2018-10-08 04:50:20
      2004       5002        200 2018-10-08 04:50:20
      2005       5003        200 2018-10-08 04:50:20

Now - if you also want to record "access denied" events, and want to use GROUP/class level permissions, too, then we need triggers (frowned upon...) or procedures (the bee's knees...).  The main reason for this being: CHECK constraints only work within a single table.  Hence, we cannot check whether a particular employee belongs to a particular group AND whether this group is allowed to access a particular location (even in your original layout, the class data and employee data are located in 2 tables ...).

For recording all valid combinations of classes and locations, we should probably add another table (your original design will contain loads of NULLs in table1).  Also, the accesslog gets an additional column, that allows us to record "access denied" events.

alter table accesslog
add allowdeny varchar2(128) default 'access granted' ;

SQL> select * from accesslog;
ID    EMPLOYEEID  LOCATIONID  ACCESS_TIME  ALLOWDENY
2000  5000        100         08-OCT-18    access granted
2002  5000        200         08-OCT-18    access granted
2003  5001        200         08-OCT-18    access granted
2004  5002        200         08-OCT-18    access granted
2005  5003        200         08-OCT-18    access granted

-- just an example!
create table locations_classes ( locationid, classid )
as
select 100, 1 from dual union all
select 200, 2 from dual union all
select 300, 3 from dual union all
select 400, 4 from dual ;

The procedure code should go into a package (which is omitted here, for clarity).  The code given below is far from perfect, but it should give you something to work with - if you have done some PL/SQL before.

create or replace procedure getAccess (
  employeeid_ number
, locationid_ number
)
is
  employeeclass_ number := 0 ;
  classfound_ number := 0 ;
begin
  select employeeclass into employeeclass_
  from employees where id = employeeid_ ;

  select count(*) into classfound_
  from locations_classes
  where rownum = 1 and locationid = locationid_ and classid = employeeclass_ ;

  if classfound_ > 0 then
    insert into accesslog ( employeeid, locationid )
    values ( employeeid_, locationid_ ) ;
  else
    insert into accesslog ( employeeid, locationid, allowdeny )
    values ( employeeid_, locationid_, 'ACCESS DENIED' ) ;
  end if;
end;
/

When testing the procedure, you may find that some calls will cause an ORA-02991.  You can disable the accesslog's FK constraint in order to allow the logging of "access denied" events.

-- parameters: employee id, location id
begin
  getAccess( 5000, 100 ) ;
  getAccess( 5003, 100 ) ;
  getAccess( 5002, 100 ) ;
  getAccess( 5004, 300 ) ;
  commit ;
end ;
/

-- ORA-02291: integrity constraint (...FKEY_ACCESSLOG) violated - parent key not found

alter table accesslog disable constraint fkey_accesslog ;

SQL> select * from accesslog ;
ID    EMPLOYEEID  LOCATIONID  ACCESS_TIME  ALLOWDENY
2024  5000        100         08-OCT-18    access granted
2025  5003        100         08-OCT-18    ACCESS DENIED
2026  5002        100         08-OCT-18    access granted
2027  5004        300         08-OCT-18    ACCESS DENIED

Sorry for the length of this answer!  The gist is: do whatever you can with integrity constraints!  Let the DDL code do the work.  CHECK constraints do not work across tables.  When you look at the (very crude) diagram, you will see that the LOCATIONS_CLASSES is not connected to the access log.  The procedure "enforces a constraint" - but not really, as we want to record "access denied" as well.

If you want to use a procedure, you may not need the PERMISSIONS table.  However, if you can get away WITHOUT writing a procedure, and only use integrity constraints, you'll get a more robust solution!


SQL Server 2005 Transactional Replication I am in the process of replicating my companies databases from SQL Server 2005 to SQL Server 2012 using Transactional Replication.  When complete we will be running preliminary tests on the database, and if things
look good, we will replicate any data that has changed since the initial replication, and then cutover to the new servers.  The replication is from our production machine which has to be up 24/7.

All of the databases replicate perfectly except one.  This database has a System.Core assembly, due to a dependencey from another assembly , since SQLCLR in SQL Server 2005 does not natively support System.Core.

I am getting the following error from the subscriber:

CREATE ASSEMBLY failed because assembly 'System.Core' is a system assembly. Consider creating a user assembly to wrap desired functionality. (Source: MSSQLServer, Error number: 6596)

I believe that this is due to the fact that System.Core has been natively supported in SQLCLR since SQL Server 2008. This error kills all of the replication onto the subscriber.

I was under the assumption that assemblies do not replicate, but apparently they do, at least for transactional replication.

Is there any way:

1) I can stop the assemblies from being published as part of the replication,

or

2) I can prevent the attempted creation of the assembly on the subscriber

or

3) I can allow the process to continue, even if the 'CREATE ASSEMBLY' fails.

Also, a stored procedure is dependent on the assemblies.  If I prevented the stored procedure from being replicated, is it possible that this would prevent the attempted replication of the assemblies it is dependent on.  I am loathe
to try it without a definitive answer, as I do not want to use our production server to play a game of "What if?"

SQL Server Replication isn't a good way to handle a database upgrade.  A much better approach would be to take a full backup and restore then do your testing.  After completing your testing you do a full backup and restore, then perform log restores until you are ready to cut over the database.  After you take the production system offline you finish the log restores then bring the database online.


Join JSONB column to normal PK column in Postgres I am trying to join a normal table to a table with a jsonb field via a property on that jsonb field in Postgres.  Example:

Normal table:

create table Res
(
    id uuid default uuid_generate_v4() not null
        constraint res_pkey
            primary key
)
;

Table with jsonb:

create table res_rem_sent
(
    body jsonb not null,
    search tsvector,
    created_at timestamp with time zone default now(),
    id uuid default uuid_generate_v4() not null
        constraint res_rem_sent_pkey
            primary key
);

create index idx_res_rem_sent
    on res_rem_sent (body)
;

create index idx_search_res_rem_sent
    on res_rem_sent (search)
;

an the body field could look like: {"resId": <GUID>, ....}

I would like to join the res table to the res_rem_sent table on resId where a res id is equal to a give GUID id.

You can use the following query:

SELECT
    *
FROM
    res
    JOIN res_rem_sent ON res_rem_sent.body->>'resId' = text(res.id) ;

JSON represents all of its contents as text, and that's the reason you have to be very careful on how the UUIDs are converted to text by PostgreSQL.

Things to take into consideration:

The ->> operator gets a JSON object field as text.
On the JSON(B) side, you need to represent the UUIDs using the standard form (as explained in UUID Type), so that conversions between UUID type to the JSON(B) text representation produces the same results.
If you want the query to be performant, you need to have at least a (functional) index on the body->>'resId' part

CREATE INDEX idx_uuid_res_rem_sent ON res_rem_sent((body->>'resId')) ;
 -- NOTE: the double parenthesis aren't optional
You get the maximum performance if you also have a text index on Res.id:

CREATE INDEX idx_uuid_res ON Res(text(id)) ;
Performance could possibly be improved if, instead of using the text representations, comparisons and indexes are done using UUID representations:

(res_rem_sent.body->>'resId')::uuid = res.id
In this case, if the conversion fails, you will not be able to insert the data into the table.

DBfiddle here to see a practical simulation of this scenario


Sql server 2008 R2 installation fails with Error: 17204 Sql server 2008 R2 installation fails with the below error message in the log message,

This is a part of the sql server log

  Starting up database 'msdb'. Error: 17204, Severity: 16, State: 1.
  FCB::Open failed: Could not open file
  e:\sql10_main_t.obj.x86fre\sql\mkmastr\databases\objfre\i386\model.mdf
  for file number 1.  OS error: 21(failed to retrieve text for this
  error. Reason: 1815).

Has anyone come across such an issue. Please help on how to resolve this issue.

EDIT: the sql server error log can be found at http://pastebin.com/TED7vJWX

EDIT:
This is the latest log,
2012-09-20 14:34:29.74 spid6s      Starting up database 'mssqlsystemresource'.

2012-09-20 14:34:29.78 spid6s      The resource database build version is 10.50.1600. This is an informational message only. No user action is required.

2012-09-20 14:34:30.51 spid6s      Error: 15209, Severity: 16, State: 1.

2012-09-20 14:34:30.51 spid6s      An error occurred during encryption.

Same as before.

This is a known issue which has several causes:

The first thing to check is your permissions on local machine:
Are You a local Administrator?
Is there a group policy that might prevent you from installing \ debugging?

If these do not resolve the problem in your case, and you have verified the required permissions, please provide more info:

What parameters do you use during installation (beside the default ones)?
Which Edition of SQL Server are You installing?
Do You use Default instance name?
On which O/S are You installing?

EDIT:
Next steps:
1. open the folder ":\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Log
2. Find a file named Detail.txt
3. This file has a complete log, You need to find the FIRST error that occured during installation.
4. Paste it here, only the part with the error.

EDIT:
Added Solution to comments, waiting to hear if the problem is solved


What is the difference between CREATE INDEX and ALTER TABLE ADD INDEX? I have searched the web but I have failed to find a conclusive answer for this question.

Is there any difference between CREATE INDEX and ALTER TABLE ADD INDEX?

Are there any problems that may arise when I use one or the other or do both work the same way?

Server version: 5.5.32 - MySQL Community Server (GPL)

The  CREATE INDEX syntax requires an index name, whereas the ALTER TABLE ADD INDEX syntax doesn't.

This question as already been asked and answered and should be marked as a duplicate.


DB2 - Using stored procedure variable in a "CREATE TABLE as (..)" Statement migrating from Netezza to DB2 we face the following problem:

CREATE OR REPLACE PROCEDURE DoStuff()
BEGIN
  DECLARE vStarttime TIMESTAMP;
  SET vStarttime = now();

  CREATE TABLE foo AS (
    SELECT
    x,
    y,
    vStarttime as t_change
    FROM
    bar
  );
END

With the basic idea being that t_change should hold the time at which the procedure was called. However, this fails because apparently vStarttime "is not valid in that context". We opted to first create the table, and then call an update to fill in the timestamp.. while this works, however, it feels very hacky, and we would like to avoid the additional update statement for performance reasons. Is there a better solution?

Note: foo is actually a quite complex temporary table. I omitted most of the details to keep it as simple as possible.

The use of an intermediary variable is not necessary here. Db2 regsitry variables, including CURRENT TIMESTAMP or its standard-compliant variant CURRENT_TIMESTAMP, are set once within the scope of a statement referencing them. This means that in a statement like yours:
SELECT
    x,
    y,
    CURRENT_TIMESTAMP as t_change
FROM
    bar

all rows will have the same value of t_change as of approximately the beginning of the statement execution.


MongoDB Using String-Type Shard Key for Zone Ranges Mongo lets any field type be used to set ranges for Shard Zones, but doesn't specify how analyses are conducted.

My goal is to point queries and writes at the exact right server, WITHOUT using a compound shard key (for various execution complexities).

I want to embed a unique user alpha-numeric ID, plus a latitude and longitude value into a single string, and use this as my shard key.

Ex: (ID.lat.lon) "4kjaj29.48.-89"

Would fall within the range:

"0000000.45.-100" to "zzzzzzz.50.-80"

Mongo uses utf8 strings, https://www.utf8-chartable.de/unicode-utf8-table.pl, in which for alpha-numeric characters 0 is the "least" and z is the "greatest".

So I have to imagine this is how range inclusion is computed? Does anyone know off-hand if this approach is correct? And if not, can you point me in the right direction to accomplish this?

There is definitely a way to accomplish this, since some kind of range inclusions calculation is occurring with string types, but just requires knowing the analysis strategy.

As per MongoDB documentation here In sharded clusters, you can create  zones of sharded data based on the shard key. You can associate each zone with one or more shards in the cluster. A shard can associate with any number of zones. In a balanced cluster, MongoDB migrates chunks covered by a zone only to those shards associated with the zone.

Some common deployment patterns where zones can be applied are as follows:

Isolate a specific subset of data on a specific set of shards.
Ensure that the most relevant data reside on shards that are geographically closest to the application servers.
Route data to shards based on the hardware / performance of the shard hardware.

For example

In the above mention figure a sharded cluster with three shards and two zones. The A zone represents a range with a lower boundary of 1 and an upper bound of 10. The B zone represents a range with a lower boundary of 10 and an upper boundary of 20. Shards Alpha and Beta have the A zone. Shard Beta also has the B zone. Shard Charlie has no zones associated with it. The cluster is in a steady state and no chunks violate any of the zones.

After configuring a zone with a shard key range and associating it with a shard or shards, the cluster may take some time to migrate the affected data. This depends on the division of chunks and the current distribution of data in the cluster. When balancing is complete, reads and writes for documents in a given zone are routed only to the shard or shards inside that zone.

Shard Key

You must use fields contained in the shard key when defining a new range for a zone to cover. If using a compound shard key, the range must include the prefix of the shard key.

For example, given a shard key { a : 1, b : 2, c : 3 }, creating or updating a zone to cover values of b requires including a as the prefix. Creating or updating a zone to covers values of c requires including a and b as the prefix.

You cannot create zones using fields not included in the shard key. For example, if you wanted to use zones to partition data based on geographic location, the shard key would need at least one field that contained geographic data.

When choosing a shard key for a collection, consider what fields you might want to use for configuring zones. After sharding, you cannot change the shard key. See  Choosing a Shard Key for considerations in choosing a shard key.

Hashed Shard Keys and Zones

When using zones on a hashed shard key, each zone covers the hashed shard key values. Given a shard key { a : 1 } and a zone alpha with a lower bound of 1 and an upper bound of 5, the bounds represent the hashed value of a, and not the actual value. Therefore, there is no guarantee that MongoDB routes documents where a has a value of 1 to 5 to zone alpha. MongoDB routes any document where the hashed shard key value falls within the range of 1 or 5 to a shard inside zone alpha.

In general, a zone covering a sequential range of hashed shard key values may exhibit unexpected behavior.

It is possible create a zone which covers the entire range of shard key values using minkey and maxkey to guarantee that MongoDB restricts all the data for a specific collection to the shard or shards in that zone.

For your further ref here, here, here and here


All queries are so slow on mysql NDB cluster Here is my situation:

I got over 10G of web logs (around 30 million rows) and I mainly search it with various conditions.

e.g.:

select * from tbl
where ip = '123.123.12.3'
  and agent = 'mozillar'
  and body like '%script%'

As data is getting bigger, a simple query takes minutes, so I am trying to create some distributed database with MySQL cluster.

I have built 1 management server, 1 SQL (api) server and 4 data nodes on a Google cloud VM. They have 2 CPUs and 8 GB per each instance.

PROBLEM:

When I try to restore data from an .sql file, each insert statement takes almost 2~3 seconds!

3 sec * 30m row = FOREVER!

The select query also disappointed me, it takes almost 100 times longer than normal InnoDB.

Am I doing something wrong?

Is there any better solution for my purpose?

config.ini

[ndbd default]
NoOfReplicas=2
DataMemory=5G
IndexMemory=128M
MaxNoOfConcurrentOperations=1000000
MaxNoOfLocalOperations=1100000
MaxNoOfConcurrentTransactions=327680
NoOfFragmentLogFiles=256
FragmentLogFileSize=32M
SharedGlobalMemory=512M
DiskPageBufferMemory=512M
DiskIoThreadPool=8

[ndb_mgmd]
NodeId=1
hostname=10.142.0.7
datadir=/var/lib/mysql-cluster/mgmt

[ndbd]
NodeId=11
hostname=10.142.0.8
datadir=/var/lib/mysql-cluster/data1
[ndbd]
NodeId=12
hostname=10.142.0.9
datadir=/var/lib/mysql-cluster/data2
[ndbd]
NodeId=13
hostname=10.142.0.10
datadir=/var/lib/mysql-cluster/data3
[ndbd]
NodeId=14
hostname=10.142.0.11
datadir=/var/lib/mysql-cluster/data4

[mysqld]
NodeId=100

All the nodes are connected to management server as expected

-- NDB Cluster -- Management Client --
ndb_mgm> show
Connected to Management Server at: localhost:1186
Cluster Configuration
---------------------
[ndbd(NDB)]     4 node(s)
id=11   @10.142.0.8  (mysql-5.6.29 ndb-7.4.11, Nodegroup: 0, *)
id=12   @10.142.0.9  (mysql-5.6.29 ndb-7.4.11, Nodegroup: 0)
id=13   @10.142.0.10  (mysql-5.6.29 ndb-7.4.11, Nodegroup: 1)
id=14   @10.142.0.11  (mysql-5.6.29 ndb-7.4.11, Nodegroup: 1)
[ndb_mgmd(MGM)] 1 node(s)
id=1    @10.142.0.7  (mysql-5.6.29 ndb-7.4.11)
[mysqld(API)]   1 node(s)
id=100  @10.142.0.2  (mysql-5.6.29 ndb-7.4.11)

Additional info:

    Create Table: CREATE TABLE `tbl` (
  `no` int(11) NOT NULL AUTO_INCREMENT,
  `rule_name` varchar(50) DEFAULT NULL,
  `ip` varchar(20) DEFAULT NULL,
  `detection_time` datetime NOT NULL,
  `uri` text,
  `site` varchar(500) DEFAULT NULL,
  `country` varchar(50) DEFAULT NULL,
  `body` text CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  `detection_string` text,
  `detection_type` int(11) NOT NULL,
  `action` int(11) NOT NULL,
  `category` int(10) NOT NULL,
  PRIMARY KEY (`no`),
  KEY `no` (`no`),
  KEY `ip` (`ip`)
) ENGINE=InnoDB AUTO_INCREMENT=21682043 DEFAULT CHARSET=utf8
1 row in set (0.03 sec)

Redundant index. A PRIMARY KEY is an index, so there is not need for the redundant KEY no(no).  (DROPping this will speed up things a little.)

Agent. Where's the agent in the schema?  Is it a varchar?  or enum?  or normalized SMALLINT UNSIGNED.  The last would save space, hence improve speed, but would complicate the insert.

Better index. Replace INDEX(ip) with INDEX(ip, agent).  This will speed up the given SELECT some.  (But I suspect the agent is usually the same for a given ip.)

Country code There are very standard 2-letter country codes; use them instead of the bulky varchar(50).

Batching. For now for the real speedup...  Batch the inserts.  Collect them in a table, swap tables, INSERT..SELECT, etc.  Details here.  That technique is self-adjusting; so if things get slower, they actions also get more efficient.  That link also explains how to efficiently normalize.

Tuning. IndexMemory=128M seems unreasonably small.

Back to InnoDB?

  The select query also disappointed me, it takes almost 100 times longer than normal InnoDB.

The techniques I have mentioned are well tuned for InnoDB; I have less experience with NDB Cluster.  Maybe you should go back to InnoDB and we can work on making that better?  innodb_buffer_pool_size should be about 70% of RAM.

IPv6. A side note:  IPv4 needs only VARCHAR(15); IPv6 needs (39).  And ip addresses may as well be CHARACTER SET ascii, not utf8.  (No significant performance benefit here.)


How to reset object permissions to default? I've just been added to a project using Postgresql 11.4 where database security has been an afterthought.  As part of tightening things up, I've used ALTER DEFAULT PERMISSIONS to set sane ACLs on any new objects (schemas, tables, functions, sequences) created.  However, there are hundreds of existing objects already in this database with wacko permissions.

Is there a command that will reset the permissions of an object (or group of objects) to the defaults (as seen with \ddp)?  Something like the equivalent of REASSIGN OWNED for object ownership, but instead for acls.

The only way I can think of is directly modifying the system catalog tables.

To remove all permissions from s.tab, you can

UPDATE pg_class
SET relacl = NULL
WHERE oid = 's.tab'::regclass;

But then you also have to remove the dependencies between the table and all roles which had privileges on the table:

DELETE FROM pg_shdepend
WHERE classid = 'pg_class'::regclass
  AND refclassid = 'pg_authid'::regclass
  AND objid = 's.tab'::regclass
  AND deptype = 'a';

Needless to say, this is very dangerous, and I cannot guarantee that I didn't forget anything…


Selecting Consective rows with same Value & calcute the difference between timestamps of same value rows.Please Check What will b error in this query? I have a table in which there are two columns
A_MSISDNs and Incoming_timestamp which is the time of call.

I want to calculate the time duration between two calls of same MSISDN
e.g:-

A_MSISDNs        Incoming_timestamp
420000128321     2018-06-26 06:26:02
420000128321     2018-06-26 07:04:44

The Time Duration between Calls by above Same MSISDN is 28 minutes.

For this purpose I made query which is giving error  1064

Kindly correct my query if there is any syntax error and logic error

SELECT MSISDN,total_count,TIMESTAMPDIFF(MINUTE, MIN(times), MAX(times)) AS duration

FROM (

SELECT t1.*
, @groupNumber := IF(@prev_MSISDN != t1.MSISDN, @groupNumber + 1, @groupNumber) AS gn
, @prev_msisdn := t1.MSISDN
FROM (

(#t1
#[ itwill returns the msisdn,incoming_timestamp of all msisdn and total count and order them according to their msisdn ,timestamp. We do this beacuse we want to retrive the different timestamp of calls of the same MSISDN
SELECT tb1.a_msisdn AS 'MSISDN',tb1.incoming_timestamp AS 'Times',tb2.total_count AS 'Total_counts'
FROM cc_info  AS tb1
JOIN
#{it will count the msisdn whose total_count>=10
(SELECT COUNT(1) AS 'Total_count',a_msisdn
FROM cc_info
GROUP BY a_msisdn
HAVING Total_count>=10
ORDER BY incoming_timestamp) AS tb2
#}
ON tb1.`a_msisdn`=tb2.a_msisdn
WHERE tb1.a_msisdn=420000128321 #it is for testing
ORDER BY tb1.a_msisdn ,tb1.incoming_timestamp
#]
) AS t1
,(SELECT @groupNumber := 0, @prev_MSISDN := NULL) var_init_subquerysa ORDER BY MSISDN,times

) AS t2
)
GROUP BY gn,MSISDN

Right before

) AS t2

is

 ORDER BY  MSISDN,times

which does not make sense.  If times is to be a table, then it should not be JOINed (with a comma) to another table with an ORDER BY clause.  (Unless you put parens around the subquery.)

Or was that supposed to be a period, not a comma?:  MSISDN.times

When Error 1064 says "near", it means it.


Updating directly from SP1 to SP3 I am considering applying the Service Pack 3 in a SQL Server 2008 with Service Pack 1; however, I was not able to find if there are any drawbacks in this process, specially regarding to Replication Services and Reporting Services.

Has someone done this kind of update? Was it smooth or did it generate many issues?

You shouldn't have any issues, just be sure that when you plan your maintenance for SQL Server you don't get blocked by the "Restart Computer" rule. E.g. if you have installed Windows Updates in the meantime you might not know that Windows needs to be restarted until you enter your maintenance window...

For some background, I got surprised by this during a SQL 2012 Service Pack + Cumulative Update series recently. It turned out that I fired up the computer, started the Service Pack install, but didn't know that Windows Update had started performing updates in the background. By the time I got to the CU, the reboot flag had been set, and I was very frustrated since it seemed to me like the SP install had forced a restart requirement.

http://www.sqlperformance.com/2012/12/system-configuration/sql-2012-slipstream


using mongorestore for a db present in my computer I just started using mongodb so my question might be a little basic.
I have a folder with the collections I want to restore(this was a zip file I downloaded from my email).
How do I use mongorestore to restore this db into my local db?

How about reading then manual.
Simples form of that command is mongorestore  directory/ where mongod is running localhost at port 27017 and that 'directory' have those dump files.


Single query sending and retreiving duplicate data on MS SQL Server? Very odd thing happening. It would appear that all queries against a particular database in our system are periodically "running slow". Ie "normal speed" for 5 minutes then slow for 5 minutes (roughly).

On further investigation (after several days of eliminating the "obvious") it would appear that, sometimes queries are being sent by the client (Sql Server Management studio) multiple times and being received multiple times.

Ie "Bytes sent from client" will double or even treble, and "bytes received from server" will do the same. Obviously similar pattern of increase in"Client processing time" and "total execution time".

This is even happening with a SELECT * FROM table where the table on has 3 rows of data!

And when one query goes slow they all go slow. Doesn't matter how simple the query / results set, or which "client" is accessing (same in an ADO.NET based console app / MVC web app).

Any suggestions would be greatly appreciated...

EDIT

The "multiple queries" thing cannot be recreated. No idea why it happened, seems to have been an anomaly which temporarily lead us down the wrong alleyway
@Brent Ozar - thank you for your suggestion - we have run these types of tests already with, unfortunately, no useful results!
@jco360 - thanks for your suggestions. Indexing is a non-starter as performance is slow across the whole database (one slow = all slow then one quick = all quick). I am the developer and I'm seeing the same issue when running simple queries from SSMS. Finally, the hardware issue may be the answer, although 64GB Ram and many processors should suffice for a server which is barely being used, there may be some hard drive corruption or similar?

It's also not network related as tests have been run from the box itself.

Try running a sql profile trace on the server activity (http://technet.microsoft.com/en-us/library/ms175047.aspx) - you may have some table locking issues caused by concurrently running queries locking tables/pages/indexes.

Try running a perfmon - there may be something/s running that is using all the available disk/IO/CPU/Memory resources when things slow down. Look for any memory swapping - this will slow things down. If it is one of these then you will need to track down the cause. Note CPU at 100% for short period of times is OK (actually can indicate optimum performance).

Also check for fragmentation and consider rebuilding indexes.

Turn off services/applications that are not needed - they use up resources that are better used for your application.


Dealing with Mysql Replication and Functions In MySQL replication Master-Slave, is there anyway to deal with the functions:

LOAD_FILE()
UUID(), UUID_SHORT()
USER()
FOUND_ROWS()
SYSDATE()
GET_LOCK()
IS_FREE_LOCK()
IS_USED_LOCK()
MASTER_POS_WAIT()
RAND()
RELEASE_LOCK()
SLEEP()
VERSION()

We have Replication and I am kind of worry for the consistency of the data, I.E in my localhost.

MASTER

mysql> CREATE TABLE `t1` (   `number_id` varchar(30) DEFAULT NULL );
mysql> insert into t1 values (uuid_short());
mysql> select * from t1;
+-------------------+
| number_id         |
+-------------------+
| 95769054064672779 |
+-------------------+
1 row in set (0.00 sec)

Slave

mysql> select * from t1;
+--------------------+
| number_id          |
+--------------------+
| 167826651055390725 |
+--------------------+
1 row in set (0.00 sec)

Master

mysql> update t1 set number_id=0 where number_id='95769054064672779';
mysql> select * from t1;
+-----------+
| number_id |
+-----------+
| 0         |
+-----------+
1 row in set (0.00 sec)

Slave

mysql> select * from t1;
+--------------------+
| number_id          |
+--------------------+
| 167826651055390725 |
+--------------------+
1 row in set (0.00 sec)

Is there anyway to deal with it? I am not a programmer, should the programmers knows that it's replication database so he can start using variables with Mysql Functions to keep the consistency?

What you're seeing is completely unacceptable, and developers should not be expected to work around it.

You've suppressed it in your answer, and I don't know if you didn't notice it, or didn't consider it significant, or something is broken in your setup.  Here's what you should have seen:

mysql> insert into t1 values (uuid_short());
Query OK, 1 row affected, 1 warning (0.11 sec)

Notice the 1 warning.

mysql> show warnings;
+-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message                                                                                |
+-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Note  | 1592 | Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system function that may return a different value on the slave. |
+-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.07 sec)

So your simple fix is "don't use statement format."

Clean up those discrepant tables, and then...

mysql> SET GLOBAL BINLOG_FORMAT = MIXED;

Then, prepare yourself for what follows... because MIXED mode is more correct and deterministic than STATEMENT mode, it's also more likely to detect cases where your servers are already inconsistent, and replication will stop.  Don't be confused that MIXED mode has broken it... MIXED mode will just tend to uncover problems you already have.

The same thing goes for ROW format, which is even more aggressive at maintaining identical data, and is my preferred BINLOG_FORMAT for that reason, among others.

You also need to set this in your my.cnf file so that the setting persists if the server is restarted.

But once you aren't using STATEMENT mode any more, this issue should completely disappear.

http://dev.mysql.com/doc/refman/5.6/en/replication-rbr-safe-unsafe.html


Is file a basic form of database? I’m having an interesting conversation with my friend who’s a developer. I’m a database admin. I say file is a basic form of database. I believe whatever that stores data can be called a database. He says only engine like sql server is a database. I think he’s confused between a database and database management system? Please share your thoughts. Am I right or wrong ? Btw he agrees an excel is a database since it has rows and columns. I say a simple word document is also a simple form of database without coming into the argument of whether it’s efficient or what are it’s capabilities!

According to Garcia-Molina (this is from memory) a database is any permanent store of data where the data is retrievable.

This, to my mind, makes a notepad file a "database". The whole issue here is context and semantics!

A database can mean pretty much whatever the person who uses the word says!

To give you another example, I used to work for an airline. The word "flight" meant 3 different things depending on whether you were talking to engineers, flight crew or sales people.

1) to an engineer, a "flight" meant an aircraft taking off and landing (one "leg")
2) to flight crew, a "flight" meant out from base and back to base (two "legs" - aka a "return")
3) to sales, a "flight" meant a season (high/low) of "flights" over a six month period - obviously many "legs" (also known as a "rotation").

Database terminology is fraught with the same issues:

Does a database have to be relational? Clearly not, although some purists might argue otherwise.
Does it have SQL or not? Does that make it a  database? Clearly there were databases prior to SQL and many current databases don't support it!
If you store a list of phone numbers in a notepad file on Google drive, is that a database? Clearly by some criteria it is - it's searchable, the data is retrievable and permanent!

My answer to your question is to be clear in your definitions before you start a detailled technical conversation - as an IT person in the airline who dealt with engineers, flight crew and sales, I always  had to stop and ask them what they meant by "flight" and then gently nudge them towards the terms "leg", "return" or "rotation".

Do the same in your conversations about databases and you'll not go wrong - "Are we talking about SQL Server or MongoDB?" &c.


Is storing data that can be inferred go against data normalization? To simplify lets say I have two tables PROJECTS and JOBS. A user creates a project and runs 1 or more jobs using a project.

So it would possible look something like this:

PROJECTS

[PK] project_id
etc.

JOB

[PK] job_id
[FK] project_id
job_run_date
etc.

Ideally I want to be able to list all projects by last used (last job run) and display them to the user. When I create a job, I can insert the job then update the project entry to have a last used; or I can just have it insert the job, then when I'm displaying all projects, I can do a join and aggregation with JOBS to get the last used date.

I figure, the drawback of the join is that as you have more jobs, performance may be slowly impacted over time to do the joins and aggregations. Whereas, being somewhat redundant increases the chance of a transaction failure and wastes a bit of space.

If I were to choose the update both tables (i.e. have a last_used` column in PROJECTS), would that be going against normalization (or good db design practices)?

Storing an attribute derived from a different table does introduce some risk of inconsistent data, if you fail to update projects after updating jobs for some reason, so you need to develop means to maintain integrity, e.g. by adding a trigger to jobs.

It does not necessarily goes against normalization principles per se, because you can model the project entity to include the last_used attribute and then your projects table remains in the same normal form as it was before.

Storing a bit of redundant data is a valid optimization technique when warranted. Computed columns, materialized views etc. fall in that category. If you exhaust other ways to make your application perform according to the specifications, you can use denormalization, knowing and mitigating its risks.


How to determine the service pack of Sql Server I am having a sql server 2012 enterprise edition installed on my win 2012 r2 server.

But i am unable to determine whether its installed with service pack or not.
how to find out which service pack is installed ?
Here is my screen shot, and my screen shot says that , its a ent.edition
with version number:11.2.5058.0 .

When I serached in msdn, i didnot see this version number in any of the service packs of sql server 2012.

You can execute

SELECT @@VERSION;

Which will give you the relevant information (note the bold, italicised SP1 from the example results)

  Microsoft SQL Server 2012 (SP1) - 11.0.3000.0 (X64)     Oct 19 2012
  13:38:57      Copyright (c) Microsoft Corporation     Developer Edition
  (64-bit) on Windows NT 6.2  (Build 9200: )


Is there a way to restrict automatic statistics updates to a certain timeframe? I have an Azure SQL Database that runs on average at < 10% DTU capacity. It seems that at random times during the day large statistics update queries will execute on the database, causing DTU (specifically data IO) resources to spike.

I have confirmed that these are statistics updates coming from the system by looking at the "Query performance insights" graphs and reports.

For example, one of these queries is:

SELECT StatMan([SC0], [SC1], [SC2], [SB0000]) FROM (SELECT TOP 100 PERCENT [SC0], [SC1], [SC2], step_direction([SC0]) over (order by NULL) AS [SB0000]  FROM (SELECT [UserId] AS [SC0], [OrganizationId] AS [SC1], [Id] AS [SC2] FROM [dbo].[Cipher] TABLESAMPLE SYSTEM (1.064647e+000 PERCENT) WITH (READUNCOMMITTED) ) AS _MS_UPDSTATS_TBL_HELPER ORDER BY [SC0], [SC1], [SC2], [SB0000] ) AS _MS_UPDSTATS_TBL  OPTION (MAXDOP 16)

Sometimes these statistics updates can have a negative effect on the production application running on top of this database. Execution times against the database and application can increase.

Since I cannot predict when these daily statistics updates will happen, it is not possible for me to schedule a maintenance window where I can increase system resources to help them complete faster. Obviously at < 10% average resource usage I am not at a point where it makes sense to increase resources and pay for a costlier tier 24/7.

I already run a scheduled job weekly during off hours (on the weekend) to rebuild indexes and update statistics manually, yet these automatic updates still seem to happen on a daily basis, often during peak times.

Is there a way I can reduce the impact of these automatic statistics updates during peak application times? Or is there a way I can better predict when they will happen? Ideally I would like to handle these types of impactful maintenance tasks during a planned window.

I would disable these auto statistics update and schedule a sql job to do on-demand stats update based on my defined rules, such as when the data change % is more than a threshold or when index fragmentation is more than a threshold.

Actually for big tables (100+ million rows), I always do this way because auto statistics update usually use a very small sampling rate for the big tables, and it often can cause more harm than good.


Why is my transaction file growing when db is in simple mode and there are no waiting transactions I noticed that my database log file has reached 160GBs. My database is in simple recovery mode and I used the following query to see if there are any waiting transactions.

SELECT d.name,d.log_reuse_wait,d.log_reuse_wait_desc
FROM sys.databases d

It returned log_reuse_wait 0 and description 'NOTHING'.
Why is my transaction log growing then?

Your file is probably large but empty.

In SSMS, right-click on your database and go into Tasks, Shrink Files. DO NOT HIT OK IN THIS DIALOG - but it's the easiest way of seeing how empty your files are. Choose Log in the dropdown, and I imagine you'll see that your log file is actually 99% empty.

You probably had a large process that grew the file to 160GB. You should probably shrink your file and then resize it to 160GB again manually (to reduce fragmentation), and then monitor how full it is using PerfMon counters.


CLR Stored Procedure on Azure SQL Managed Instance error on execution: "Assembly in host store has a different signature than assembly in GAC" I have a CLR stored procedure which executes correctly when deployed to local SQL Server instances from SQL Server 2012 - 2017. I can successfully deploy to an Azure SQL Managed Instance but when I execute the procedure I get the following error:

  Could not load file or assembly 'System.Net.Http, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a' or one of its dependencies. Assembly in host store has a different signature than assembly in GAC. (Exception from HRESULT: 0x80131050).

I have tried playing around with the project references but to no avail - the only version of the System.Net.Http.dll that can be deployed to the Managed Instance is one that errors on execution.

One of the requirements / nuances of working with SQLCLR is that any assembly loaded into both the GAC and SQL Server must be the exact same version (i.e. down to the patch level, not just Major.Minor.*). So, your local instances might all be using 4.7.2 (or whatever), but if the Managed Instance is using 4.7, 4.7.1, 4.8, or anything else, then you will get that error. You would even get this error if one of your local instances was on a server running a different version of the .NET Framework than the one that you grabbed the System.Net.Http.DLL from. Of course, if all of these different instances you mentioned are running on the same physical server, then there is only one OS involved anyway, so of course they all work correctly ;-).

You will need to find out what specific version is being used on the Managed Instance and use that in your Azure install script. You shouldn't need to use the same version locally since the reference to a similar version that has the same signatures should work.

Now, how to find the exact Framework version on Managed Instance? The following query seems to be the best way to get that info:

SELECT olm.[name], olm.[file_version]
FROM   sys.dm_os_loaded_modules olm
WHERE  olm.[name] LIKE N'%mscoreei.dll%';

O.P. replied back that the query above returned a version of 4.7:2623.0 on the
Managed Instance, and a version of 4.7:3190.0 on the local (on prem) system.

This is the source of the "Assembly in host store has a different signature than assembly in GAC" error

So, in order for this to work, you need to find that specific version of that DLL and package it up to load into the Managed Instance.

P.S. This issue is one of the downsides to using unsupported .NET Framework libraries. They are going to be in the GAC (usually, right?) and if a Windows update (locally, or whatever they do to the host system of the Managed Instances) updates the version then your SQLCLR project stops working. And, if the new unsupported Framework DLL gets converted to a mixed mode assembly (both managed and unmanaged code), then you won't be able to load the new version into SQL Server and will need to re-code your project to not use that unsupported Framework assembly.

P.P.S. What are you using System.Net.Http for? If it's for web services stuff, then you should just use the System.Net.HttpWebRequest and HttpWebResponse classes. You will have to do a little extra coding to construct and parse the XML of the web request, but those classes exist in a library that's fully supported.

O.P. replied back with: "Correct, web services stuff."

Later, O.P. replied back with: "I've managed to successful deploy and execute a basic SQLCLR
procedure on Managed Instance using just HttpWebRequest and HttpWebResponse,
I think it'll be best to go down that line until Microsoft extend the list of natively supported libraries.."

P.P.P.S. You might want to take a look at SQL# (a SQLCLR library that I wrote), as there is a stored procedure that handles this, based on HttpWebRequest. It handles most (if not all) internally managed HTTP headers, allows for sending custom headers (including userid/password, if necessary), sending POST / GET data, and even some other stuff that most people usually leave out. Just a thought, though for full disclosure, while there is a Free version, INET_GetWebPages is only in the Full (paid for) version.


Unable to change database owner to a user that is already mapped as user I ran into this problem.  Trying to change a database owner to acct1; however, I get the following error message when doing so:

  The proposed new database owner is already a user in the database

Now I know one solution is to drop the acct1 user and then change the database owner, but I can't do that because all the tables in this database are owned by the acct1 schema.

How can I get around this?

You can easily change the schema ownership to dbo, then you should be able to drop the user, and add them back using ALTER AUTHORIZATION:

ALTER AUTHORIZATION ON SCHEMA::[schema_they_own] TO dbo;
DROP USER [user_name];
ALTER AUTHORIZATION ON DATABASE::[database_name] TO login_name;

Now, they will be the dbo user in the database (and will have automatic ownership of the schema).

You may need to change more than one schema; you can generate a script to do this using dynamic SQL.

DECLARE @sql NVARCHAR(MAX) = N'';

SELECT @sql += N'ALTER AUTHORIZATION ON SCHEMA::'
  + QUOTENAME(s.name) + N' TO dbo;
'
FROM sys.schemas AS s
INNER JOIN sys.database_principals AS dp
ON s.principal_id = dp.principal_id
WHERE dp.name = N'database user name';

PRINT @sql;
-- EXEC sys.sp_executesql @sql;

If you are happy with the command generated, you can copy and paste the output and run it, or you can uncomment the EXEC and run it again.


SQL Server Data Transactional Replication of huge data, locking down table We have setup data transactional replication from Server-A(SQL Server 2008 R2) to Server-B(SQL Server 2008 R2). We have a SQL Job scheduled to run everyday on Server-A, which executes single UPDATE command which causes ~120K record updates on a specific table. Now the problem here is, when that single UPDATE command gets executed on Server-A it updates nearly 120K records within few seconds. This is a single transaction on Server-A but it is equivalent to ~120K transactions on Server-B. This causes, the table on Server-B gets lock down for more than 20-30 min. everyday, whenever that SQL Job runs.

I read about MaxCmdsInTran option, but it isn't a recommended one, so cant use it. As an alternate option, we are planning to exclude that particular table from Data Replication and we are planning to execute that SQL Job on both Server-A and Server-B.

I am just wondering if there is any better solution available for this problem.

You may want to look into Publishing Stored Procedure Execution in Transactional Replication which can provide significantly better performance for large batch operations since only the procedure execution is replicated, bypassing the operation being sent to Subscribers as a large, multi-step transaction.

If you have anymore questions please let me know.  I hope this helps.


Get detailed output of SQL Agent job We have an SQL Server Agent job that runs a maintenance plan to reindex all of the database on the server. Recently this has failed but the job history is not providing enough information to diagnose the problem.

In the job history it states The job failed. The job was invoked by user foo\bar. The last step to run was step 1 (Rebuild Index).

In the details window are multiple messages in the following form:

Executing query "ALTER INDEX [something] ON [a...".: 0% complete  End Progress  Progress: 2015-03-15 22:51:23.67     Source: Rebuild Index Task

The SQL statement that is running is truncated, and I would assume that the output of the statement is also truncated, preventing me being able to identify which particular statement has failed and why. Is there any way to extract the full text of these messages?

Is there any way to extract the full text of these messages?

You can achieve it in 2 ways - Go in job step and select the Advanced tab:

a. Output to a file (<== My preferred method)

b. "Log to table" and "Include step output in history" (<== You need to trim msdb..sysjobhistory in long-run as the messages are stored as nvarchar(max) instead of nvarchar(1024))

In order to see the additional logged information you need to use this stored procedure sp_help_jobsteplog or you could query the msdb.dbo.sysjobstepslogs table directly.

More info here


MySQL slave replicates changes that are in neither binlog_do_db nor replicate_do_db I have a standard simple master slave replication that usually runs fine.
But sometimes, all of a sudden, I get replication errors on the "phpmyadmin" table.

I have explicitly only specified one database in replicate_do_db and in binlog_do_db. So it should only log and process that database. Why am I getting errors on another table?

This is the output of master status and slave status:

mysql> show master status \G
*************************** 1. row ***************************
            File: mysql-bin.001473
        Position: 84364707
    Binlog_Do_DB: my-database
Binlog_Ignore_DB:
1 row in set (0.00 sec)

mysql> show slave status \G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: my-master-host
                  Master_User: pm-rep
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.001473
          Read_Master_Log_Pos: 82124707
               Relay_Log_File: mysqld-relay-bin.000486
                Relay_Log_Pos: 38975372
        Relay_Master_Log_File: mysql-bin.001473
             Slave_IO_Running: Yes
            Slave_SQL_Running: No
              Replicate_Do_DB: my-database
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 1146
                   Last_Error: Error 'Table 'phpmyadmin.pma_column_info' doesn't exist' on query. Default database: 'my-database'. Query: 'DELETE FROM `phpmyadmin`.`pma_column_info` WHERE `db_name` = 'my-database' AND `table_name` = 'users' AND `column_name` = 'INDEX''
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 38975226
              Relay_Log_Space: 82125053
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 1146
               Last_SQL_Error: Error 'Table 'phpmyadmin.pma_column_info' doesn't exist' on query. Default database: 'my-database'. Query: 'DELETE FROM `phpmyadmin`.`pma_column_info` WHERE `db_name` = 'my-database' AND `table_name` = 'users' AND `column_name` = 'INDEX''
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 5309510
1 row in set (0.00 sec)

Replication filtering isn't bulletproof. Due to how the filtering is implemented the events responsible for your errors are being generated because the default database at query runtime is the my-database schema as expected and the query being executed is fully qualified INSERT INTO phpmyadmin.pma_column_info...

Peter Zaitsev explains the scenario well in this post:

Filtered MySQL Replication


Storing Procedure Fields That Were Used I am creating a database for production work (specifically lab testing).
Most Work is for production, therefore performed strictly according to the Procedure for that Product.  By itself, this is easy to model. The Work references the Procedure as it contains how the work is done:

Example Schema
Work:      Work_id, Procedure_id, {other non-relevant fields}
Procedure: Procedure_id, Product_id, Machine_id, Material_id, RunMinutes

Two exceptions (overrides and special testing) add much complexity to the design.
Question: Given the two exceptions below, how should I store the Procedure fields that were actually used for each Work?

Exception - Overrides:
Sometimes the required equipment or components are not available. In these cases, the manager can approve a one-time override for equivalent substitutes.  Examples:

Machine X was broken. Perform the Work by hand.
We ran out of Material Y. Use Material Z instead.
Keep runtime at 45 minutes

The database must capture how the Work was actually performed.
I see three possible options:
Option 1: Store Locally: The Work references the original Procedure. Each Work also locally stores the Procedure fields used, including any modifications. This creates many duplicates, but you have a local "snapshot" for each Work.
Example Schema
Work_id | Procedure_id    | Machine | Material | RunMinutes
1       | 1               | By-Hand | Z        | 45

Procedure_id | Product_id | Machine | Material | RunMinutes
1            | 1          | X       | Y        | 45

Option 2: Single Use Procedure:  The original Procedure is copied to a new Procedure, marked inactive, and modified with the overrides. The Work then references the new Procedure.  This maintains the Work.Procedure_id for how the Work was performed.
Example Schema
Work_id | Procedure_id
1       | 2

Procedure_id | Product_id| Active| Machine | Material | RunMinutes
1            | 1         | Y     | X       | Y        | 45
2            | 1         | N     | By-Hand | Z        | 45

Option 3: Store as Overrides: The Work points to the Procedure and optionally points to a ProcedureOverride table. For each field in Procedure, if there is an override, then use it otherwise, use the Procedure value.
Example Schema
Work_id| Procedure_id| Override_id
1      | 1           | 1

Procedure_id| Product_id| Machine | Material | RunMinutes
1           | 1         | X       | Y        | 45

Override_id             | Machine | Material | RunMinutes
1                       | By-Hand | Z        | NULL

Query: ActualWork
Work_id   |Procedure_id | Machine | Material | RunMinutes
1         |             | By-Hand | Z        | 45

Exception - Special Testing:
For non-standard work (such as research and development), there is no specific Procedure. Again, the database again must capture how the Work was actually performed.
I see two options (equivalent to the respective options above)
Option 1: Store Locally: Each Work locally stores all Procedure fields used. The user must input values for each field.
Option 2: Single Use Procedure: A new Procedure is created, marked inactive, and populated by the user. The Work then references the new Procedure.  This maintains the Work.Procedure_id for how the Work was performed. Keep in mind however, there is no actual (real world) Procedure for the non-standard Work.

So this seems like largely a question about proper data normalization.

With data normalization in mind, we can easily rule out option 1. The data, like you suggest, will clearly be unnormalized and repeated.

Option 3 looks to be more of a hack to handle the current schema not capturing your domain. If you need to implement hacks like this, it's definitely time for a redesign.

Which brings us to Option 2. Of the three options, this seems to be the best. However, the schema seems like it could be capable of further proper normalization. Without knowing more about the domain, I would assume that there probably is some sort of functional dependency between Machine, Material, and RunTime. Or it could involve the product ID. Of course if all 4 of these values are free to differ without affecting the others then this area for schema optimization can be ignored.

If no functional dependencies exist in the procedure table, then the schema as suggested in option 2 with a new 'inactive' procedure to capture custom work appears to be the best overall choice