How do I avoid excessive zeroes for numeric fields? As per recommendations, I always use the numeric type for any column supposed to hold a sum of money.
When inserting such values (or updating them), I always use high precision math functions in my application (PHP "BC Math").
This results in the fields getting values such as:
-1494.744565020000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000

I view them later in pgAdmin 4 and it's very annoying to see the long series of pointless zeroes, when it should be just:
-1494.74456502

There is no meaning with storing/representing all those zeroes like that. I'm not fully sure if this is PG or pgAdmin's doing, actually.
Either way, I want to avoid it. How do I prevent numeric values from being stored with pointless zeroes?
(Of course, I don't want less precision -- I'm just talking about zeroes that literally have no meaning and thus should be automatically cut off from the right until they hit a non-zero digit.)
Is PG for some reason storing these numeric numbers like this? If so, why?

As mustaccio pointed out, this is more of an issue of the displaying of the value on the client side. Your number is originally generated with a lot of unneeded 0s in PHP. The column that stores this data in database has a fixed amount of precision it must adhere to (per its schema definition), so all values are stored in that column with the same precision (and technically normalized with the same amount of 0s). This isn't apparent though until recalled back into the client application. It is the client side that is responsible for formatting.
If you really wanted to avoid this, you could store your results in a VARCHAR column instead and cast your results to a string then use some sort of function in PHP to remove the trailing 0s like RTRIM() or REGEX.
This isn't necessarily recommended from a Normalization standpoint for performance and maintainability reasons, but if you don't need to manipulate these results in SQL or do any aggregation on it after it's stored then it's probably ok.


Migrate small sets of data from production to staging/sandbox I have a development environment that consists of a production, staging and development servers that host SharePoint 2010 or 2013 as well as SQL Server 2008 R2. These each need to have relevant data, though there is limited bandwidth and hardware price restrictions that we must abide by.

Is there any way we can take a small portion of production data, and transfer it to our staging and development environments? And by small portion, I mean, say, the last month of data. So the storage impacts will be minimal as well as time consumption to do additional backups and/or transfer times. Our production database is a couple terabytes.

Possible restrictions:

Limited bandwidth for transfers between domains (prod -> dev)
Huge database sizes
SharePoint: simply keeping the schema and having the last month of updated data will cause a lot of orphaned / corrupted data, so there has to be some kind of seeded data to keep integrity

The overall goal is to reduce the database size of development and staging servers, while being able to capture the most recent data.

Preferred solutions; scripts or software.

The first thing you need to identify in your db are which tables require all their data (the seed data, sometimes called "domain" data), and which tables are suitable for bringing only recent data. You have a couple choices then.

1: Use a tool like SSDT to deploy your SCHEMA to dev & staging, keep the domain data as a bunch of INSERT statements in SSDT and apply those, and then write a custom SSIS package (or use the data transfer wizard, which is just SSIS) to pull only the useful, recent data over the wire.

PRO: this will definitely meet your requirement to pull only the
minimum necessary data.
CON: lots of queries will go over the wire on SSIS, have
to develop separate packages for pretty much every table. Have to keep the packages up-to-date as schema changes hit your db.

2: Backup the whole database, restore it somewhere else, and run a purge script. Then backup the purged db and restore this to dev & staging.

PRO: a pretty simple solution, requiring only DELETE scripts for the older data.
CON: requires another server with enough space to restore your full
backup, lots of time taken in first restore, probably time taken in
delete.

3: Partition your big tables so that data older than a month goes to a new filegroup, and use the Piecemeal restore option to only restore your PRIMARY filegroup.

PRO: A very simple restore process.
CON: Requires design work before the fact to partition the tables.

In our production environment, we have 1.5TB database, and we use option 2. I have tested option 3 with smaller db's and it does work rather well. Option 1 requires no changes to your db, but seems like the most work to me.


How do I choose primary keys for star schema BI data warehouse? I am extracting sales turnover data from a Microsoft Dynamics Database into another SQL database that will be used by the BI tool of choice.

This data is coming from G/L (general ledger) entry items.
Each entry contains information for the amount, date, branch, and sales division.

In the DW, I intend to use a star-schema, where the sales figure per day is given in dollars. (fact).

DaySales_Fact

DaySalesId
Sales (Fact)
Date
BranchCode
SalesDiv

Branch_Dim

BranchCode
BranchName

Assume the same thing for Date and SalesDiv tables.

The source data is unreliable and historical data may change at any time. The data warehouse is emptied each day for this reason, and restocked with the live data. This process is scheduled to run once daily.

The source data uses a varchar(2) to store the BranchCode as a primary key.

Would it be best practice to use the business branch code, or create an auto incrementing primary key (see below).

All of the branches are refreshed daily too.

DaySales_Fact

DaySalesId
Sales (Fact)
DateId (fk)
BranchId (fk)
SalesDivId (fk)

Branch_Dim
    BranchId - auto inc PK
    BranchCode
    BranchName

Almost always surrogate keys (your "Auto Incrementing primary key") are best in data warehouses. I have seen very few exceptions where this is not the case (but some do exist) - yours does not seem to be exceptional. To answer why would be repeating stuff you can find all around the web, by the giants in the field, for instance: http://www.kimballgroup.com/1998/05/surrogate-keys/


Slow SQL Server 2008 R2 backups after Windows update I recently installed a series of Windows Updates on our SQL Server 2008 R2 server.  Note, these were not SQL Server updates, they were just the usual monthly Windows updates.  These updates included a couple of re-boots, but otherwise this was just routine maintenance.  Since then however, SQL Server has been running at least 4 times slower than usual - backups and queries - and I'm at a loss to figure out why.  Backups which used to take 3 hours now take 20.  I appreciate there are a myriad reasons why queries can be slow on a restart, but backups?

I've heard there were issues with the Balanced Power Plan when upgrading from SP2 to SP3 with SQL Server 2008 R2, but this server has had SP3 installed for over a year now.  I tried changing to the High Performance Plan anyway, but that made no difference to the speed of the backups.

I also checked if there were any pending sp_configure changes that would kick-in (or kick-out) after a restart, but the Error Logs did not show any changes.

Is it possible that the Network Adapters would somehow reset after a re-boot?  One of the Windows updates was for the "Intel Ethernet Server Adapter X520-2".  Could that be an issue?  Would the Jumbo Frames size, or anything other configuration value, reset itself after a driver update?

To give more information about the slow backups, the biggest database I backup is about 3TB, hosted on one Windows drive letter.  That Windows drive is made up of six 820GB spindle drives (strictly speaking, it's twelve drives in a RAID-1 formation).  Previously, this database would backup in about 3.5 hours at a rate of 192 MB/sec.  Now it takes 20 hours at a rate of 32 MB/sec, and I don't understand what's changed.  It seems that in the past, data must have been pulled from all six drives simultaneously during the backup to achieve 192 MB/sec, but now I'm guessing the backup is pulling data from just one drive at a time (checking with PerfMon), hence 32 MB/sec.  Why?  Could this have been caused by a change in the RAID controller, the network adapters, the SQL Server backup command itself, or what?  And is it typical for data to be read/written from an array of drives in a parallel fashion?  If so, what piece of hardware controls this?

This is a bit of a fishing expedition but here would be my troubleshooting process assuming you don't have your own stats to help clear things up.

Check the history of database size and compressed backup size from your msdb catalogs. Have they changed over the past few weeks.

Sometimes an application will go bezerk and fill a database with garbage; what was 100GB is now 1TB. SCOM and Sharepoint are good examples of this.
Sometimes someone can go in and turn off compression on your instance or in whatever your backup software is, especially if they are trying to force you to use de-duplication on a SAN.

You need to talk to the staff who are managing the Infrastructure and SAN.

If this is a VM they may have loaded the VM server with another host that is chewing up resources.
Their VM monitoring software may be logging useful statistics that will help you narrow things down.
The SAN people usually have their own built in monitors that may be able to add some detail.

If you're still stuck with no metrics of any kind, go back to your msdb information and use the timings to calculate an MB/s for backups. Now take a copy only backup to NUL (note: this will put a negative load on your system of course) and compare.

If your backup to NUL speed is below your previous MB/s speed you'll know your read speed of the database has changed and is likely the bottleneck (though there may be others, it's a starting point).
Otherwise you'll know the server itself is fine and that you need to refocus your attention in the direction of network IO and the destination SAN.

If all of this fails for some reason...

You can see what updates were installed in Add/Remove Programs and do some Googling on the KBs to see what turns up. I don't think you will find anything but who knows; maybe?
Start capturing new metrics. You could use the built in Management Data Warehouse functionality seeing as you have nothing else. You could use a trial of a paid tool.
If you still had to do it manually, you could capture the disk IO DMVs (sys.dm_io_virtual_file_stats) using sample scripts from the net to work out your IO wait times, and see if they're abnormally high like over 100ms which would point at a local disk (or SAN) issue, or intensive queries. Then you can use other DMVs (and sample scripts) to pull out intensive queries from the plan cache and see if you can spot anything strange.
Have any new staff been added, or a new developer, or even to an existing AD group that has been granted access, or a new login on the server (check sys.server_principals)? I've seen new people point Excel and other tools at a production database and kill it without realising the impact.
It's possible, but unlikely, you're in a situation where the first few queries that came in had parameters that created poorly optimised plans which are now doing table scans over and over and dragging down performance. If you got desperate, you can also free your plan cache (seeing as the system is in dire straights anyway) and see what happens. I've never seen this drag a system down before but I have seen it kill individual applications before.

As you can see, if you have no previously recorded information about how the system should be acting, it's a matter of thinking of everything it could possibly be, and working out how you're going to rule that in or out through expensive and time wasting investigation. But them's the breaks.


What is the difference between Schema and Database in Oracle? In Oracle, datafile are system files where actual data is stored. The collection of datafiles make tablespace and, at last, database is collection of tablespaces.

Correct me if I am wrong on the concepts of datafile, tablespace and database.

I would like to understand the difference between Schema and Database in details. Online resources are helpful but seemed confusing, regarding this difference.

You can think about the schema as a user home directory in Unix. A schema and oracle user are strongly tight together. One user maps to a single schema. You can temporarily change the current schema in a similar way as you do in Unix by ALTER SESSION SET CURRENT_SCHEMA=USER1.

Objects in other schemas can be reached in the very same way as in Unix: Just precede the object name with SCHEMA. eg: USER1.TABLE1, USER2.TABLE2 would be an example. After you change the current schema to USER1 you don't need to append the USER1 prefix anymore.

A database is the most wide container, it collects schemas-users, tablespaces (datafiles are part of a tablespace) and one or more (RAC) instances.

Things get more complicated with 12c multitenant option because you can have multiple pluggable databases (PDB) in a single container database (CDB). But it is just another story.


How to escape XML characters in TSQL before converting to XML? I have XML structure which is input param for my stored procedure. It contains element with & (which is escaped in XML). When I extract that element to VARCHAR I receive & which is not valid XML character. I need to escape it, before converting to XML again. How to do that without REPLACE?

I have the following text: param1=xyz&para2=dasdasdfdas&param3. It is a part of query string. I converted it to XML and send it as a part of XML structure:

<zzz xmlns="http://example.com">
  <aaa>aaa</aaa>
  <bbb>param1=xyz&amp;para2=dasdasdfdas&amp;param3</bbb>
</zzz>

Inside the stored procedure I need to extract it. I do that with:

ISNULL(NULLIF(LTRIM(RTRIM(@XMLInput.value('declare default element namespace "http://example.com"; (zzz/bbb)[1]', 'NVARCHAR(250)'))), ''), '');

I after that the value contains normal text (&-s are not escaped - &).

After some processing I need to put that string inside other XML. I make:

CAST( ... AS XML);

Because &-s are not escaped I got error.

Use for xml path to create XML instead of casting.

select @YourVariable for xml path(''), type

Empty string in the path expression and the absence of an alias on the returned column will give you your string back as xml.


Subtracting an integer in my db by 1. SQL I want to subtract the follower count of a user by 1, or increase it by but I don't want to do this:

   $sql = $conn->prepare('SELECT folllower_num FROM users WHERE user_id = ?');
   $sql->execute($user_id));
   //then grab the follower_num value, save it to a variable, and subtract 1
   //from it and then:
   $sql = $conn->prepare('UPDATE users SET follower_num = ? WHERE user_id =
   ?;');
   $sql->execute($newfollowercount, $user_id));

or something like that. To me this seems bad to do because while the value is saved into this code and then changed and put back into the database, someone else could have followed or unfollowed and then the number becomes incorrect. Is there another way I can do this?

SQL allows to update the number of rows at once:

UPDATE users
   SET follower_num = follower_num + 1
 WHERE user_id IN (1,2,3,4...)

WHERE clause can contain any conditions you want. All users that satisfy that conditions will be updated.


Can a view affect ALTER TABLE commands? I provide a database system to a number of customers.  The database is installed locally on Oracle.

We periodically upgrade the database structure (either adding new fields, renaming old fields, changing things around), as the product grows.

One of my customers wishes to add their own custom views to the database.  They've assured me they will add the view to a different schema to the live schema.  However, I am concerned that the inclusion of a view might affect future upgrade scripts.

If a view references a table that is to be altered, will the presence of the view prevent the table from being altered?

No, it shouldn't prevent the table from being altered. Though, if you drop the underlying table a view depends on, or alter/remove the columns from the table the view uses, the view can become invalid. You can check what views are invalid in your system with the following query:

sql> select name from dba_objects where object_type = 'VIEW' and status = 'INVALID';

You should fix the problems before the view can be made VALID. The view can be compiled manually:

sql> alter view foo compile;

Or you can allow the system automatically compile it when someone attempts to query the view. However manual compilation is the recommended way because it reduces the execution time of the first query. Usually views and other invalid objects (procedures, triggers etc.) are recompiled in bulk using a supplied script utlrp and this is a routine task for a DBA.

If you want to create the view but the base table doesn't yet exist, you can use the option FORCE:

sql> create force view foo as select * from moo;


Who has deleted MySQL user from management console? How can I find who has removed a SQL login from MySQL instance?

Checked event viewer but cannot find anything.

There is no audit trail, so you are out of luck this time.  For the future:

Plan A:  Build some kind of audit trail.

Plan B:  Look for an audit plugin, such as PAM.


looking for a cleaner way to select from multiple tables I'm currently using this code to select from 2 tables using a stored procedure.

DECLARE @now as varchar(26)
set @now = CONVERT(VARCHAR(26), SYSDATETIME(), 9)

--Select 'SMD_' + replace(convert(varchar(7),DATEADD(MONTH, -1, GETDATE()),102), '.', '_')

DECLARE @table1 AS varchar(12)
SET @table1  = 'SMD_' + replace(convert(varchar(7),DATEADD(MONTH, -0, GETDATE()),102), '.', '_')

DECLARE @table2 AS varchar(12)
SET @table2  = 'SMD_' + replace(convert(varchar(7),DATEADD(MONTH, -1, GETDATE()),102), '.', '_')

DECLARE @table3 AS varchar(12)
SET @table3  = 'SMD_' + replace(convert(varchar(7),DATEADD(MONTH, -2, GETDATE()),102), '.', '_')

DECLARE @SQLQuery AS varchar(MAX)
SET @SQLQuery = '

insert into SMDTemp (QDATE, UPC2, AWEEK, WEEKRANGE, MOVEMENT, COST, RTL, LBS, GP)
SELECT
''' + @now + ''' as QDATE, UPC2,MAX(RTG_WKD.AWEEK),RTG_WKD.WEEKRANGE, SUM(SMVNUM) AS MOVEMENT, ROUND(AVG(SMVCOST), 2) AS COST, ROUND(AVG(SMVSUGS / SMVSUGNS), 2) AS RTL, CASE WHEN SUM(smvwgt) = 0 THEN SUM(smvnum) ELSE SUM(smvwgt) END AS LBS, (ROUND(AVG(SMVSUGS / SMVSUGNS),2) - ROUND(AVG(SMVCOST), 2)) / CASE WHEN round(AVG(smvsugs / smvsugns), 2) = 0 THEN NULL ELSE round(AVG(smvsugs / smvsugns), 2) END AS GP
FROM ' + @table1 +'  INNER JOIN RTG_WKD ON SMVDATE = RTG_WKD.WKDDATE6
where UPC2 = ''' + @search + '''
and SMVStore = ''' + @store + '''
GROUP BY RTG_WKD.WEEKRANGE, UPC2
union all

SELECT
''' + @now + ''' as QDATE, UPC2,MAX(RTG_WKD.AWEEK),RTG_WKD.WEEKRANGE, SUM(SMVNUM) AS MOVEMENT, ROUND(AVG(SMVCOST), 2) AS COST, ROUND(AVG(SMVSUGS / SMVSUGNS), 2) AS RTL, CASE WHEN SUM(smvwgt) = 0 THEN SUM(smvnum) ELSE SUM(smvwgt) END AS LBS, (ROUND(AVG(SMVSUGS / SMVSUGNS),2) - ROUND(AVG(SMVCOST), 2)) / CASE WHEN round(AVG(smvsugs / smvsugns), 2) = 0 THEN NULL ELSE round(AVG(smvsugs / smvsugns), 2) END AS GP
FROM ' + @table2 +'  INNER JOIN RTG_WKD ON SMVDATE = RTG_WKD.WKDDATE6
where UPC2 = ''' + @search + '''
and SMVStore = ''' + @store + '''
GROUP BY RTG_WKD.WEEKRANGE,  UPC2

Order by weekrange desc
'

EXECUTE(@SQLQuery)

SELECT * from SMDTemp
where QDATE = @now
and UPC2 = @search

delete from SMDTemp
where QDATE = @now

I now need to add a 3rd table, and potentially up to 12 tables. The problem is when I add a 3rd table with

UNION ALL

SELECT
''' + @now + ''' as QDATE, UPC2,MAX(RTG_WKD.AWEEK),RTG_WKD.WEEKRANGE, SUM(SMVNUM) AS MOVEMENT, ROUND(AVG(SMVCOST), 2) AS COST, ROUND(AVG(SMVSUGS / SMVSUGNS), 2) AS RTL, CASE WHEN SUM(smvwgt) = 0 THEN SUM(smvnum) ELSE SUM(smvwgt) END AS LBS, (ROUND(AVG(SMVSUGS / SMVSUGNS),2) - ROUND(AVG(SMVCOST), 2)) / CASE WHEN round(AVG(smvsugs / smvsugns), 2) = 0 THEN NULL ELSE round(AVG(smvsugs / smvsugns), 2) END AS GP
FROM ' + @table3 +'  INNER JOIN RTG_WKD ON SMVDATE = RTG_WKD.WKDDATE6
where UPC2 = ''' + @search + '''
and SMVStore = ''' + @store + '''
GROUP BY RTG_WKD.WEEKRANGE,  UPC2

I can execute the query in SQL Server Management Studio but when I run it through a stored procedure the request times out.

Is there a cleaner or more efficient way of doing this? Sample code will suffice, I can plug the data in myself.

First, I would remove the Order by weekrange desc as it is unnecessary (since you aren't also using TOP() in any of the SELECT queries.

Also, why use UNION ALL in the first place? Why not just have these be 3 - 12 separate INSERT INTO table SELECT .... queries?

And, given that the only thing changing in each query is the name of the table, and that name follows a pattern, the dynamic query can be templated and then just loop through the number of months you need.

The following should do what you want, with each INSERT...SELECT from each separate SMD_ table being a separate query / transaction:

DECLARE @Search NVARCHAR(10) = N'_search_', -- just to get the test code working
       @Store NVARCHAR(10) = N'_store_'; -- just to get the test code working

DECLARE @Now DATETIME2;
SET @Now = SYSDATETIME();

DECLARE @QueryTemplate AS NVARCHAR(MAX)
SET @QueryTemplate = N'
INSERT INTO SMDTemp (QDATE, UPC2, AWEEK, WEEKRANGE, MOVEMENT, COST, RTL, LBS, GP)
  SELECT @Now_tmp AS [QDATE], UPC2, MAX(RTG_WKD.AWEEK), RTG_WKD.WEEKRANGE,
         SUM(SMVNUM) AS [MOVEMENT], ROUND(AVG(SMVCOST), 2) AS [COST],
         ROUND(AVG(SMVSUGS / SMVSUGNS), 2) AS [RTL],
         CASE WHEN SUM(smvwgt) = 0 THEN SUM(smvnum) ELSE SUM(smvwgt) END AS [LBS],
         (ROUND(AVG(SMVSUGS / SMVSUGNS),2) - ROUND(AVG(SMVCOST), 2)) / CASE
             WHEN round(AVG(smvsugs / smvsugns), 2) = 0 THEN NULL
              ELSE round(AVG(smvsugs / smvsugns), 2) END AS [GP]
  FROM SMD_{{TableDate}}
  INNER JOIN RTG_WKD
          ON SMVDATE = RTG_WKD.WKDDATE6
  WHERE UPC2 = @Search_tmp
  AND   SMVStore = @Store_tmp
  GROUP BY RTG_WKD.WEEKRANGE, UPC2;
';

DECLARE @SQLQuery NVARCHAR(MAX);
SET @SQLQuery = N'';

DECLARE @MonthsBack INT;
SET @MonthsBack = 0;

WHILE (@MonthsBack > -12)
BEGIN
  SET @SQLQuery = @SQLQuery + REPLACE(@QueryTemplate,
                                      N'{{TableDate}}',
                                      REPLACE(CONVERT(NVARCHAR(15),
                                                      DATEADD(MONTH,
                                                              @MonthsBack,
                                                              GETDATE()),
                                                      102),
                                              N'.',
                                              N'_')
                                              )
                            + NCHAR(0x0D) + NCHAR(0x0A);

  SET @MonthsBack = @MonthsBack - 1;
END;

PRINT @SQLQuery; -- debug

EXEC sp_executesql
  @SQLQuery, -- define the query
  N'@Now_tmp DATETIME2, @Search_tmp VARCHAR(100), @Store_tmp VARCHAR(100)', --param list
  @Now_tmp = @Now, -- pass in parameter: param_name = local_variable_name
  @Search_tmp = @Search, -- pass in parameter: param_name = local_variable_name
  @Store_tmp = @Store; -- pass in parameter: param_name = local_variable_name

This query was originally using EXEC(@SQLQuery) but has been parameterized to use sp_executesql (as mentioned by @Jean in a comment on the Question).


Deploying transparent data encryption I have an installation of SQL 2016 Enterprise and my boss want to deploy TDE.  I googled online and all the instruction said about "you can use a self sign certificate for test/dev purpose".  Most guide just said backup the master key and cert and that's it.

I have two SQL Server, they sit on two separate domain.  I want to make sure the database encrypted on one server can be restored on the other server.

What is the proper way and step that should be applied to a production server.  I think there is more than just creating a self sign cert and back it up somewhere safe.

This article has a great step through exactly how to enable TDE.  This includes generating keys/certificates, everything.  When you turn TDE on, it will start encrypting in the background as well as when pages are accessed or flushed to disk.

Also, sidenote, TempDB also gets encrypted so if you have other DBs/SPs that aren't encrypted and use TempDB, there may be a slight performance hit when you lay data down in TempDB and pick it back up.  You're on 2016 so you still get native backup compression with TDE (hooray!)

http://joeysbasisblog.blogspot.com/2013/10/enable-or-disable-transparent-data.html


how do i create Periods and weeks (think business reports) using 2 date ranges Basically Fromdate is every Sunday and ToDate is every saturday, i need to programatically create the PERIOD column on the fly through a stored procedure where period is the month and week is the week of that month,

--but there shouldnt be 5 weeks in a period.
FromDate    ToDate      PERIOD
2015-11-29  2015-12-05  P12-WK1 <-- see how it goes to the next period
2015-11-22  2015-11-28  P11-WK4
2015-11-15  2015-11-21  P11-WK3
2015-11-08  2015-11-14  P11-WK2
2015-11-01  2015-11-07  P11-WK1

I tried using the code below but my results are slightly off,

for example

2016-01-17  2016-01-23  P1-WK4 should be

2016-01-17  2016-01-23  P1-WK3 (notice the WK)

CASE datediff(week, dateadd(month, datediff(month, 0, dbo.GP_MASTER.FromDate), 0), dbo.GP_MASTER.FromDate) + 1
WHEN 5 THEN 'P' + CAST(DATEPART(M, dbo.GP_MASTER.FromDate + 1)AS varchar(20)) + '-WK'+ CAST(datediff(week, dateadd(month, datediff(month, 0, dbo.GP_MASTER.FromDate), 0), dbo.GP_MASTER.FromDate)  AS varchar(20))
WHEN 6 THEN 'P' + CAST(DATEPART(M, dbo.GP_MASTER.FromDate + 2)AS varchar(20)) + '-WK'+ CAST(datediff(week, dateadd(month, datediff(month, 0, dbo.GP_MASTER.FromDate), 0), dbo.GP_MASTER.FromDate) - 4 AS varchar(20))
ELSE        'P' + CAST(DATEPART(M, dbo.GP_MASTER.FromDate)AS varchar(20)) + '-WK'+ CAST(datediff(week, dateadd(month, datediff(month, 0, dbo.GP_MASTER.FromDate), 0), dbo.GP_MASTER.FromDate) + 1 AS varchar(20))
END AS PERIOD

2016-02-21  2016-02-27  P2-WK4
2016-02-14  2016-02-20  P2-WK3
2016-02-07  2016-02-13  P2-WK2
2016-01-31  2016-02-06  P2-WK1
2016-01-24  2016-01-30  P1-WK4
2016-01-17  2016-01-23  P1-WK4
2016-01-10  2016-01-16  P1-WK3
2016-01-03  2016-01-09  P1-WK2

This is my desired results

FromDate    ToDate      PERIOD (Period# - Week#)
2016-02-21  2016-02-27  P2-WK4
2016-02-14  2016-02-20  P2-WK3
2016-02-07  2016-02-13  P2-WK2
2016-01-31  2016-02-06  P2-WK1
2016-01-24  2016-01-30  P1-WK4
2016-01-17  2016-01-23  P1-WK3
2016-01-10  2016-01-16  P1-WK2
2016-01-03  2016-01-09  P1-WK1

I would suggest that you create a Calendar Table that you can use for decades to come.  You create it once and use if for almost forever.

Examples:

Aaron Bertrand's Creating a date dimension or calendar table in SQL Server

Himanshu Sharma & Mohammad Nizamuddin in TechNet T-SQL: Calendar Table


Postgres not using expression index I am using Postgres 9.5. It is possible this is related to the changes in 9.6 pointed out by PostgreSQL not using index during count(*), but I cannot be sure yet.

I have a table that has a character varying column called text. I am attempting to optimize queries which have to perform a regex on this column.

This often looks like

select * from table where text ~ 'some|key|words|we|want';

or the inverse

select * from table where text !~ 'some|key|words|we|want';

sometimes in joins

select category, count(*)
from other_t join table on (other_t.table_id = table.id)
where table.text ~ 'some|key|words|we|want';

select category, count(*)
from other_t join
    (select id
     from table
     where table.text ~ 'some|key|words|we|want') as ti on (other_t = ti.id);

Notice that the regex is always the same.

I do not want to index the column directly because AFAIK indexing text columns results in a relatively huge index that would just be a waste of space. This rules out a partial index per the linked post above. So, I've been trying to use an expression index, which I should be able to do because the regex is always the same.

However, I can't get Postgres to use the index!

I've done a vacuum analyze, I've tried every type of query I can imagine, but it always just does a Seq Scan.

The column averages ~85 characters and typically only has one or two of the keywords we're looking for if it is a match.

Any ideas?

I am not interested in full keyword search. We have a finite, stable list of words we're looking for. I don't want to make an enormous trigram/ts_vector index on this text column when all I really need is an expression index that Postgres decides to use.

We cannot eliminate the text column because it has other uses. We have already thought of adding a column which is essentially a cache of this query, but that's just ugly. If that is the best option, so be it, but we really should be able to use an index here.

Index Creation

create index text_col_idx on table ((text ~ 'some|key|words|we|want'));

Explain Analyze

explain (analyze, verbose) select id from table where text ~ 'some|key|words|we|want';

Results

Seq Scan on public.table  (cost=0.00..405624.24 rows=5190575 width=4) (actual time=0.113..52582.259 rows=5113014 loops=1)
  Output: id
  Filter: ((table.text)::text ~ 'some|key|words|we|want'::text)
  Rows Removed by Filter: 4728206
Planning time: 10.938 ms
Execution time: 52766.607 ms

Community wiki answer:
Your condition only eliminates about 50% of the rows in the table. In that case the Seq Scan will be faster than the Index Scan.
See for example this Stack Overflow answer, where user a_horse_with_no_name says:

If the SELECT returns more than approximately 5-10% of all rows in the table, a sequential scan is much faster than an index scan.
This is because an index scan requires several IO operations for each row (look up the row in the index, then retrieve the row from the heap). Whereas a sequential scan only requires a single IO  for each row - or even less because a block (page) on the disk contains more than one row, so more than one row can be fetched with a single IO operation.

Also see Index-Only Scans in the documentation, which includes:

In principle, index-only scans can be used with expression indexes. [...] However, PostgreSQL's planner is currently not very smart about such cases. It considers a query to be potentially executable by index-only scan only when all columns needed by the query are available from the index.

A separate idea would be to have a keywords table alongside your main one, with one ID per word you are interested in, and an ID related to a specific text, and just populate it as needed. The choice will obviously depend on many other constraints that only you can know.


Dynamic Pivot SQL table Table dbsize loading with data of every database size every night.

dbsize (id, database_name, database_size, insert_date)

It looks like this:

 - 1   dbname1   1000   1/1/1900
 - 2   dbname2   2000   1/1/1900
 - 3   dbname3   3000   1/1/1900
 - 4   dbname1   1200   1/2/1900
 - 5   dbname2   2200   1/2/1900

... etc

I need to get result like this

 - insert_date dbname1 dbname2 dbname3
 - 1/1/1900      1000      2000     3000
 - 1/2/1900      1200      2200

Tried with this sql, but nothing:

declare @ColumnNames NVARCHAR(MAX) = ''

declare @SQL NVARCHAR(MAX) = ''

select @ColumnNames += QUOTENAME(database_name) + ',' from  dbo.dbsize

set @ColumnNames = LEFT(@ColumnNames, LEN(@ColumnNames)-1) set @SQL =
'select * from ( select     database_name   ,insert_date    ,id     from
dbo.dbsize) as BASEDATA

PIVOT   (

    count(id)   for database_name   in      ('

        +@ColumnNames +

        ')  as PivotTable'

execute sp_executesql @SQL

You were so close. Only a missing parenthesis at the very end of the dynamic sql string, the finishing parenthesis for the PIVOT.

If you need to be able to handle multiple rows in dbsize with same database_name, you have to add a COUNT column and GROUP BY in your basedata query and change from COUNT(id) to SUM(count_column) in your PIVOT.

DECLARE @ColumnNames nvarchar(MAX) = ''
DECLARE @SQL nvarchar(MAX) = ''

SELECT @ColumnNames += QUOTENAME( database_name) + ',' from (select distinct database_name from dbo.dbsize) t
SET @ColumnNames = LEFT(@ColumnNames, LEN(@ColumnNames)-1)
SET @SQL = 'SELECT * FROM ( SELECT database_name, COUNT(*) AS cnt FROM dbsize GROUP BY database_name) AS basedata
            PIVOT ( SUM(cnt) FOR database_name IN (' +@ColumnNames + ')) AS PivotTable'
EXECUTE sp_executesql @SQL

SQL Fiddle


Fix RESOURCE_SEMAPHORE I ran sp_Blitz and on the top I got:

  Finding:  Poison Wait Detected: RESOURCE_SEMAPHORE

  Details:  0:12:36:38 of this wait have been recorded. This wait often
  indicates killer performance problems.   This means that SQL Server
  ran out of available memory to run queries, and queries had to wait on
  available memory before they could even start…

My questions are:
-Is the time in minutes? Is it a number to worry? Can I conclude just by this that I need to buy more memory for the server?

(Server has 30GB installed RAM).

Instance has only been up for 2 weeks…
EXEC sp_BlitzFirst @Seconds = 30, @ExpertMode = 1

EXEC sp_BlitzFirst @SinceStartup = 1

Where should I redirect my efforts?

Thanks for using sp_Blitz!

RESOURCE_SEMAPHORE is indeed a tough wait to run into.

  -Is the time in minutes?

The time is DD:HH:MM:SS, so you've had about 12 hours of it total.

  Is it a number to worry?

Your server has spent 12 hours waiting to get enough memory to run queries. That means queries that appear to be running are actually sitting idle waiting to get memory. How long has your server been up for?

  Can I conclude just by this that I need to buy more memory for the
  server?

That's one 'easy' way to deal with it, but you need to dig into what's going on when that wait type pops up.

For that, you'll want to try using sp_BlitzFirst.

To get total metrics since startup:

EXEC sp_BlitzFirst @SinceStartup = 1

To sample metrics during a 30 second window:

EXEC sp_BlitzFirst @Seconds = 30, @ExpertMode = 1

You could just plain not have enough memory here -- 30 GB of memory isn't much (for instance, my laptop has 32 GB of memory just to look at gifs and have 100 Chrome tabs open).

How much data are you storing on there?

You could also have awful queries using ridiculous amounts of memory.

To dig into those (if you're on a pretty recent build of a fairly modern version of SQL Server), you could run

EXEC sp_BlitzCache @SortOrder = 'memory grant' to find queries with large memory grants.

Hope this helps, and thanks again!


Create a table from a CSV file with headers I'm seeking to find a way to generate a new MySQL table solely based on the contents of a specified CSV. The CSV files I'll be using have the following properties;

"|" delimited.
First row specifies the column names (headers), also "|" delimited.
Column names & order are not fixed.
The number of columns is not fixed.
Files are of a large size (1 mil rows / 50 columns).

In Excel this is all rather simple, however with MySQL it does not appear to be (no luck with Google). Any suggestions on what I should be looking at?

You can use csvsql, which is part of csvkit (a suite of utilities for converting to and working with CSV files):

Linux or Mac OS X
free and open source
sudo pip install csvkit
Example:  csvsql --dialect mysql --snifflimit 100000 datatwithheaders.csv > mytabledef.sql
It creates a CREATE TABLE statement based on the file content. Column names are taken from the first line of the CSV file.

To extend on ivansabik's answer using pandas, see How to insert pandas dataframe via mysqldb into database?.


Why is `SELECT @@IDENTITY` returning a decimal? I'm using Dapper to execute the following query against a SQL Server 2008 R2 Express instance from a ASP.NET MVC 3 (.NET 4.0) application.

INSERT INTO Customers (
         Type, Name, Address, ContactName,
         ContactNumber, ContactEmail, Supplier)
VALUES (
         @Type, @Name, @Address, @ContactName,
         @ContactNumber, @ContactEmail, @Supplier)

SELECT @@IDENTITY

The call to connection.Query<int>(sql, ...) is throwing an Invalid Cast Exception. I've debugged it and it's at the point where Dapper calls GetValue on the returned SqlDataReader.

The return type of GetValue is Object, inspecting it in the debugger show's it's a boxed decimal.

If I change the select to SELECT CAST(@@IDENTITY as int), the return of GetValue is a boxed int and the exception isn't thrown.

The Id column is definitely of type int; Why would SELECT @@IDENTITY return a decimal?

Some additional information:

The database is brand new.
The Customers table is the only object I've added to it. There are no other (user) tables, views, triggers or stored procedures in the database.
There are 10 rows in the database, there Id's are 1,2,3,4,5,6,7,8,9,10 (i.e. the column isn't beyond the limits of an int).

My table definition is

CREATE TABLE [dbo].[Customers](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Type] [int] NOT NULL,
    [Name] [nvarchar](255) NOT NULL,
    [Address] [nvarchar](1000) NOT NULL,
    [ContactName] [nvarchar](255) NOT NULL,
    [ContactNumber] [nvarchar](50) NOT NULL,
    [ContactEmail] [nvarchar](255) NOT NULL,
    [Supplier] [nvarchar](255) NOT NULL,
 CONSTRAINT [PK_Customers] PRIMARY KEY CLUSTERED
(
    [Id] ASC
)WITH (
    PAD_INDEX  = OFF,
    STATISTICS_NORECOMPUTE  = OFF,
    IGNORE_DUP_KEY = OFF,
    ALLOW_ROW_LOCKS  = ON,
    ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

@@identity returns a numeric(38,0).  You'll need to cast it to get it to an int.
SELECT CAST(@@identity AS INT)

Also, try using scope_identity instead.  If you have any triggers on the Customers table, you may end up getting the last identity from another table.

Finally, since you're using dapper, you'll want to wrap all of that inside a stored procedure so that you are guaranteed to execute the insert and then the select on the identity in the same batch.
Theoretically, it should work most of the time to execute both of those on their own.  But issues could arise if you have to go to the database twice.  (E.g. how does this work with connection pooling?  What about dropped connections? etc.)  If you just throw it all in a stored procedure, you won't have to worry about that extra effort down the road.


How to monitor how much data is transfered in a linked server? I have a linked server to a remote computer and doing some insert, update and delete operations between local and remote databases.
I want to know how much data is transfered when operation is doing. How can I do it?
My local database is Sql server 2008 and remote database is SQlserver2ksp3

This sounds like a job for PERFMON, which is included with the latest windows server offerings.

http://technet.microsoft.com/en-us/library/cc749249.aspx

Basically you're going to setup a counter to watch the MSSQL data transfer between the servers.

You may be able to use a counter inside of MSSQL to track this as well, but I'm not familiar with any functionality that provides that.


SSIS execution error email I am a SQL Server DBA.  I maintain a large number of SSIS packages created by developers.

Over the weekend, many SSIS jobs failed since the jobs were attempting to insert/update values that resulted in duplicate data / key violations.

Normally, I would go through the execution report, and send a screen shot to the concerned developer. How can I create an automated process that will send an email to them about the error on packages as they occur?

You can modify the jobs to send email on failure. The problem with that approach is that you're going to know that job failed but why it failed will be a mystery until you get to a computer, VPN into your network, fire up SSMS, grab a screenshot and send it to the responsible party.

A rough framework I've used follows

sp_send_dbmail
SSISDB views
sql agent running periodically

This does stuff

DECLARE
        @profile_name sysname = 'SQLAdmins'
,       @recipients varchar(max) = 'billinkc@friedChicken.com'
,       @copy_recipients varchar(max) = NULL
,       @blind_copy_recipients varchar(max) = NULL
,       @subject nvarchar(255) = 'failed package test'
,       @body nvarchar(max) = 'Stuff has failed, fix please'
,       @body_format varchar(20) = NULL
,       @importance varchar(6) = 'NORMAL'
,       @sensitivity varchar(12) = 'NORMAL'
,       @file_attachments nvarchar(max) = NULL
,       @query nvarchar(max)
   = N'
SELECT
    O.object_name AS FailingPackageName
,   O.object_id
,   O.caller_name
,   O.server_name
,   O.operation_id
,   OM.message_time
,   EM.message_desc
,   D.message_source_desc
,   OM.message
FROM
    SSISDB.catalog.operation_messages AS OM
    INNER JOIN
        SSISDB.catalog.operations AS O
        ON O.operation_id = OM.operation_id
    INNER JOIN
    (
        VALUES
            (-1,''Unknown'')
        ,   (120,''Error'')
        ,   (110,''Warning'')
        ,   (70,''Information'')
        ,   (10,''Pre-validate'')
        ,   (20,''Post-validate'')
        ,   (30,''Pre-execute'')
        ,   (40,''Post-execute'')
        ,   (60,''Progress'')
        ,   (50,''StatusChange'')
        ,   (100,''QueryCancel'')
        ,   (130,''TaskFailed'')
        ,   (90,''Diagnostic'')
        ,   (200,''Custom'')
        ,   (140,''DiagnosticEx Whenever an Execute Package task executes a child package, it logs this event. The event message consists of the parameter values passed to child packages.  The value of the message column for DiagnosticEx is XML text.'')
        ,   (400,''NonDiagnostic'')
        ,   (80,''VariableValueChanged'')
    ) EM (message_type, message_desc)
        ON EM.message_type = OM.message_type
    INNER JOIN
    (
        VALUES
            (10,''Entry APIs, such as T-SQL and CLR Stored procedures'')
        ,   (20,''External process used to run package (ISServerExec.exe)'')
        ,   (30,''Package-level objects'')
        ,   (40,''Control Flow tasks'')
        ,   (50,''Control Flow containers'')
        ,   (60,''Data Flow task'')
    ) D (message_source_type, message_source_desc)
        ON D.message_source_type = OM.message_source_type
WHERE
    OM.operation_id =
    (
        SELECT
            MAX(OM.operation_id)
        FROM
            SSISDB.catalog.operation_messages AS OM
        WHERE
            OM.message_type = 120
    )
    AND OM.message_type IN (120, 130);
'
,       @execute_query_database sysname = NULL
,       @attach_query_result_as_file bit = 0
,       @query_attachment_filename nvarchar(260) = NULL
,       @query_result_header bit = 1
,       @query_result_width int = 256
,       @query_result_separator char(1) = char(13)
,       @exclude_query_output bit  = 0
,       @append_query_error bit = 0
,       @query_no_truncate bit = 0
,       @query_result_no_padding bit = 0
,       @mailitem_id int = NULL
,       @from_address varchar(max) = NULL
,       @reply_to varchar(max) = NULL;

EXECUTE msdb.dbo.sp_send_dbmail
    @profile_name
,   @recipients
,   @copy_recipients
,   @blind_copy_recipients
,   @subject
,   @body
,   @body_format
,   @importance
,   @sensitivity
,   @file_attachments
,   @query
,   @execute_query_database
,   @attach_query_result_as_file
,   @query_attachment_filename
,   @query_result_header
,   @query_result_width
,   @query_result_separator
,   @exclude_query_output
,   @append_query_error
,   @query_no_truncate
,   @query_result_no_padding
,   @mailitem_id OUTPUT
,   @from_address
,   @reply_to;

So what'd we do? We found the last SSIS package that ran from the SSISDB that failed (message_type 120 = failed)

        SELECT
            MAX(OM.operation_id)
        FROM
            SSISDB.catalog.operation_messages AS OM
        WHERE
            OM.message_type = 120

We then collected relevant bits of information (all error and failure messages) about the run with the rest of the query against the SSISDB objects.

That gives enough information for someone to know the package that failed, what folder it was in, when etc. But notice that SSISDB query is assigned to a variable.

I'm going to use that variable as a parameter to sp_send_dbmail When I invoke that proc with the query parameter, I'm going to get an email telling me something broke (which is the level of info I get out of the job step) but then I have an attachment which will be the actual results of the query. Now that's pretty handy.

Left as an exercise for the user is to create a "already notified" list of operation_ids so you don't keep spamming people. Possibly another table that associates folders and or/packages with a list of recipients. This approach assumes only one package runs at a time. That's a silly assumption but twas valid when I wrote this. It would be better to have two tables: Failed and Reported. Collect the unique operation ids into the Failed table every 5 minutes. Every 30 minutes collect those IDs, send emails and move them into the Reported table. Now you have a history that you've processed them.


Trying to fit indexes in memory: partitioning vs compression or neither? I have a "transaction" table that grows at about 150k rows-per-day rate. 97% of user aggregate queries refer to the last 3 months of data and must run very efficiently. Trimming older transaction data is not an option as I need to make it available going back 12 years. /Splitting "recent" from "old" transactions into separate tables is an option, but I'm trying to avoid this complication if possible./

Would you recommend table partitioning to help keep indexes in memory? Currently, the table has bigint primary key, so if I go with partitioning by date-range, I'd have to add datetime to the PK, which would double PK's memory footprint -- feels kind of counter productive.

Or, perhaps table compression would help better? Specifically, MySQL 5.7 permits page-level compression for InnoDB tables that reside in file-per-table tablespaces (mine do).

Increasing system memory is not an option for a while as I'm pinned to an AWS reserved instance for time being.

Indexes and data are stored in pages. Buffer pool holds the "hottest" pages meaning that if you only access one quarter of your table frequently mostly the "hot" part of your table is going to be the buffer pool because an LRU list manages which page(s) should be evicted at any given time.

Partitioning is a good alternative which also helps data cleanup (simply dropping a partition) but yes as you said it comes with some restriction.

If you have auto increment primary key you can also set up ranges on that. Since you have quite good estimation on how many rows you have inserted / day it should be easy to calculate ranges which translates nicely into date ranges.

Experiment with the setup and see if it does improve the performance.

InnoDB compresses on page level but there are many gotchas there:

1) you should check first if your dataset is compressible enough to have benefit. InnoDB user KEY_BLOCK_SIZE which means if it can compress below this than it will otherwise won't.

2) To minimize disk IO both compressed and uncompressed version of the page is stored in the buffer pool meaning that with assuming at least 50% compressability you sacrifice 33% of your buffer pool on the altar of compression which may become an issue. This can be improved with better compression rates. For example with key_block_size=4 only 20% but that means every page has to be minimum 1:4 compressable.

From http://dev.mysql.com/doc/refman/5.5/en/innodb-compression-internals.html:

  Thus, at any given time, the buffer pool might contain both the
  compressed and uncompressed forms of the page, or only the compressed
  form of the page, or neither.

A good explanation of the scenario is available in this answer: https://serverfault.com/questions/358444/setting-mysql-innodb-compression-key-block-size

3) Table compression can also lead to serious mutex contention issues. For more details: https://www.percona.com/blog/2011/05/20/innodb-compression-woes/

Therefore I wouldn't go for compression unless you have big varchar columns that you want to have indexed. You can compress either in application level or on the filesystem both has their own benefits and both are much more efficient than InnoDB compression.


Executing a stored procedure inside another stored procedure from a linked server I'm having a problem and I don't know how to solve it, I have searched the web and found good advice but I can't work it out.

This is the problem: I have a SQL Server instance running on my PC, and I linked one of the main servers SRVOLD\SQLDESA to it. I want to execute main server's stored procedures from my PC's SQL Server instance and insert the results into a new table. I found the perfect way to do it using the following:

SELECT *
INTO Bank
FROM OPENQUERY([SRVOLD\SQLDESA],
               'EXEC Bank_Database.Bank.usp_GetTDcodes 1, 5')
GO

There is important information about this server, it's SQL Server version is 2008. Keep this in mind for later.

Ok so I managed to executed this Stored Procedure but I found out something, turns out that inside this Stored Procedure there's an execution of another stored procedure, check this out:

1st stored procedure:

CREATE PROCEDURE Bank.usp_GetTDcodes
    (@code TINYINT = NULL, @qty TINYINT = NULL)
WITH ENCRYPTION
AS
    DECLARE @@msg VARCHAR(100)
    DECLARE @@OK INT
    DECLARE @@today CHAR(30)

    SELECT @@today = CONVERT(VARCHAR(30), GETDATE(), 112) + ' ' +
                     CONVERT(VARCHAR(30), GETDATE(), 8)

    SELECT bnk_code, bnk_descr
    FROM CODBNK
    WHERE bnk_code < 50

    EXECUTE @@OK = Bank.usp_WriteEvent  @qty, @code, @@today, 500

    IF @@OK <> 0
        RETURN @@OK

RETURN 0
GO

Now let's look inside the 2nd stored procedure:

CREATE PROCEDURE Bank.usp_WriteEvent
    (@code TINYINT = NULL,
     @qty TINYINT = NULL,
     @date DATETIME = NULL,
     @number SMALLINT = NULL,
     @ideve INT = 0 OUTPUT)
WITH ENCRYPTION
AS
    DECLARE @@sdate VARCHAR(30)
    DECLARE @@ret SMALLINT

    INSERT INTO Event (eve_code, eve_qty, eve_date, eve_number)
    VALUES (@code, @qty, @date, @number)

    SET @@ret = @@error

    IF @@ret = 0
    BEGIN
        SELECT @ideve = @@IDENTITY

        SELECT @@sdate = CONVERT(VARCHAR(30), @date, 112) +  ' ' +
                         VARCHAR(30), @date, 8)
    END
    ELSE
        RETURN @@ret
GO

When I executed the 1st stored procedure, I was able to insert it's result into a new table, but I was hoping to find a new row inserted in the table Event, because that is the expected result when executing 2nd stored procedure.

So I started to search online and managed to achieve this by doing the following:

SELECT *
INTO Bank
FROM OPENQUERY([SRVTEST\SQLDESA],
               'SET FMTONLY OFF;SET NOCOUNT ON;EXEC Bank_Database.Bank.usp_GetTDcodes 1, 5')
GO

So, the SET FMTONLY OFF;SET NOCOUNT ON worked and I was happy. But something happened...

I needed to execute the same stored procedure, but this time adding a new linked server SRVNEW\SQLDESA. This server's version is 2012, so the new solution didn't work. I kept trying and trying different ways, there's just one way to make it work and is the following:

EXEC [SRVNEW\SQLDESA].[Bank_Database].Bank.usp_GetTDcodes 1,5

But it doesn't work for me because I need the 1st stored procedure result into a new table. And I don't know its schema that's why SELECT INTO works best for me. I don't know what else I can do, maybe is the OPENQUERY that doesn't work? Do I need to change something else?

PD: I also tried using OPENROWSET didn't work either.

Thanks in advance, and have a nice day! Peace!

Some references: http://www.sommarskog.se/share_data.html#OPENQUERY

I used the function that @Scott Hodgin suggested and created the table with that information. Thanks man!

Note: if your Stored Procedure is using a Temporary table this won't work as specified in the page:

  The result could not be determined because the batch contains a
  temporary table and is not supported by sp_describe_first_result_set.


Is it possible to change recursion level of a CTE in a function? I have created a function that takes a string and it returns a table with the position and character of each letter in the string. However as CTE recursion is limited to 100 levels, it fails if a string passed in is longer than that. I know I can use OPTION (MAXRECURSION [value]) to change the limit, but this does not appear to work when defining the function.

My function:

CREATE FUNCTION [dbo].[StringSplit]
(
    @String NVARCHAR(MAX)
)
RETURNS TABLE
AS
RETURN
(
    WITH Split(Pos,Digit)
    AS(
        SELECT 1 AS Pos, LEFT(@String, 1) AS Digit
        UNION ALL
        SELECT Pos + 1, RIGHT(LEFT(@String, Pos + 1), 1)
            FROM Split
            WHERE Pos < LEN(@String)
    )
    SELECT Pos, Digit FROM Split
    ---OPTION (MAXRECURSION 0) -- Unable to create function if this line is UnCommented)
)

Thus the following sql code will fail due to hitting the 100 default limit:

SELECT * FROM Impact_Work.dbo.StringSplit('How long of a sentence can I possibly think of for testing thingymadoodlers out. Yes I do love the word thingymadoodlers so don''t judge me!')

Now if I add the option, it will work

SELECT * FROM Impact_Work.dbo.StringSplit('How long of a sentence can I possibly think of for testing thingymadoodlers out. Yes I do love the word thingymadoodlers so don''t judge me!')
OPTION (MAXRECURSION 0)

However, it is highly unlikely that those using this function will remember to add this extra bit to their queries so I am hoping to add this in the function it self. Is this possible? (And yes, I do ramble wildly when I need to come up with some string for testing)

This is what Mikael was hinting at, I believe.

You will find things work a lot quicker without that MAX parameter, are you really going to need that big a string?

CREATE FUNCTION [dbo].[StringSplit]
(
    @String NVARCHAR(MAX)
)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN
(
  WITH E1(N) AS (
                 SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
                 SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
                 SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
                ),                          --10E+1 or 10 rows
       E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows
       E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max
 cteTally(N) AS (SELECT TOP (ISNULL(DATALENGTH(@String),0)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4)
        SELECT N AS Pos, substring(@String, N, 1) AS Digit
        from cteTally
);


Optimize a query that is using sub-query in where clause I have been trying to find a way to optimize this query by probably removing the subquery in where clause.

it currently takes 2 minutes to run this query with the where clause and 4 seconds without the where clause.

SELECT
    rateString,
    currency
  FROM myTable.trades ct
  WHERE ct.id IN
      (
        SELECT top 1 id
        FROM myTable.trades tt
        WHERE tt.currency = ct.currency
        AND tt.isExecuted = 1
        AND tt.status LIKE 'executed'
        AND tt.transactionType LIKE 'sell'
        ORDER BY tt.created_at DESC
      )
ORDER BY currency, created_at DESC

The subquery will limit the result to the top 1 row per
currency with some more where and order clause.

Everything is from a single table and no foreign key is involved

You can try the following:

WITH CTE_Trades
AS (
    SELECT  ct.rateString
    ,       ct.currency
    ,       RowNum  = ROW_NUMBER() OVER (PARTITION BY ct.currency ORDER BY ct.created_at DESC) -- try with ct.id too
    FROM    myTable.trades ct
    WHERE   ct.isExecuted = 1
    AND     ct.status = 'executed'
    AND     ct.transactionType = 'sell'
)

SELECT rateString, currency FROM CTE_Trades WHERE RowNum = 1


I'm a beginner, studying Data modelling as part of my course. I'm having trouble understanding Normalisation. How can i improve? It was difficult to frame the title given the word limit, so apologies for that.

Currently I'm studying DBA and Data modelling as a part of my ICT Apprenticeship course in a Web Development team. I'm kind of grappling with understanding Normalisation. I understand the concept, of eliminating redundant data, stopping anomalies, using keys and relationships, but in practice I'm struggling. In an effort to help me understand, I've tried to make and normalise a small table of information:

My UNF table:

My attempt at normalising the table to 3NF:

So, questions. I suppose straight away, from an expert, am I doing this right? Or am i completely goofing up? Or am i settling in the middle?

Right now I'm worried that, in this example, the 'Assignments' table has Employee ID as a Foreign Key, meaning Porject ID would have to be the Primary. But that seems counter intuitive seeing as the 'Projects' table should be using Project ID as the primary key. And I'm not quite sure how i should proceed in a situation like this.

Apologies if this question is too rookie/generally not appropriate for the forum. I've had some trouble finding basic resources that cover this material.

Many thanks.

Just looking at an example of a table is a wrong way of normalizing. You should understand the meaning of the fields to properly normalize. For instance you should know if the salary is the same for all the persons that have a certain role, or if it differs from person to person (in the above example we cannot know, since each role has only a person, so that both interpretations are possible).

To know the meaning of the properties you have two routes: either know the meaning of the elements of the domain, through a high level model of it, (for instance through an Entity-Relationship model or a UML model), or to have formalized this knowledge through the definition of the functional dependencies holding between the properties.

In your example there are two points that are not clear (that this that are not clear from the example table): is the salary related to a role or to a person? Is the Est. Time related to a project or to the fact that an employee works for a certain project for that estimated time? If the correct anwer to those question is the first one in both cases, your schema is correct (or normalized). Otherwise you must revise it.


Determine if queries have small or large results In the MySQL documentation for Query cache, specifically on the topic of query_cache_min_res_unit  "If you have a lot of queries with small results, the default block size may lead to memory fragmentation" & "If most of your queries have large results (check the Qcache_total_blocks and Qcache_queries_in_cache status variables)"

#my.cnf
query_cache_size = 20M
#SQL
SHOW STATUS LIKE "%qcache%";
Qcache_free_blocks  4250
Qcache_free_memory  13633864
Qcache_queries_in_cache 7027
Qcache_total_blocks 18381

Is it correct interpretation that total_blocks / queries_in_cache = average number of blocks for each cached query (in my case 2.6)? Would it make any sense to increase query_cache_min_res_unit from the default 4096?

edit
I don't believe the above interpretation is correct any more. A block can be any size between query_cache_min_res_unit (4K) to query_cache_limit (default 1MB) and each of query, result and table will consume a block. Therefore total_blocks / queries_in_cache shows the uniqueness of tables in queries - i.e as there are more queries on the same table, total_blocks / queries_in_cache would approach 2.
Two different queries using the same and only 1 table- (3 + 2 = 5 = total blocks) / (2 = queries in cache) = 2.5
Ten different queries using the same and only 1 table- (3 + 2 + 2 + 2 + 2 + 2 + 2 + 2 + 2 + 2) / 10 = 2.1

However I'm still struggling to reconcile this with my memory values as I would have thought that some approximation of the average query size would be given by the formula

 (query_cache_size - Qcache_free_memory) / Qcache_queries_in_cache
 (20971520 - 13633864) / 7027 = 1044.2

Since 1044 is below the query_cache_min_res_unit, I must be wrong (or bad at maths, or both). And besides, this doesn't count for the each blocks consumed by the query and table(s). I think that a more accurate formula is below, but of course this gives a negative so again, something is off.

(
 (query_cache_size - Qcache_free_memory) / Qcache_queries_in_cache) -
 (Qcache_queries_in_cache - Qcache_queries_in_cache) * query_cache_min_res_unit
)

Conclusion so far
Very unlikely to change any settings as I can't make sense of it. I would be thankful if the average size of queries in cache can be determined from these values as it seems like it should be possible - but my numbers don't add up.

ANALYSIS

Look back at your question where you said

  "If you have a lot of queries with small results, the default block size may lead to memory fragmentation" & "If most of your queries have large results (check the Qcache_total_blocks and Qcache_queries_in_cache status variables)"

Here is the context right from the MySQL Documentation

  If you have a lot of queries with small results, the default block size may lead to memory fragmentation, as indicated by a large number of free blocks. Fragmentation can force the query cache to prune (delete) queries from the cache due to lack of memory. In this case, decrease the value of query_cache_min_res_unit. The number of free blocks and queries removed due to pruning are given by the values of the Qcache_free_blocks and Qcache_lowmem_prunes status variables.

  If most of your queries have large results (check the Qcache_total_blocks and Qcache_queries_in_cache status variables), you can increase performance by increasing query_cache_min_res_unit. However, be careful to not make it too large (see the previous item).

Look at your numbers

Qcache_total_blocks : 18381
Qcache_free_blocks : 4250
Qcache_queries_in_cache 7027

Given these numbers

23.12% (4250 / 18381) of the Query Cache is fragmented
There are 2.6158 (18381 / 7027) blocks per query

On average, you can interpret this to mean one of the following:

76.88% (100 - 23.12) of the blocks have data
76.88% (100 - 23.12) of each block has data

Each individual block's fragmentation cannot be fixed. On the other hand, the query cache's total fragmentation can be adjusted by doing one thing the Documentation says (which I highlighted and italicized) : decrease the value of query_cache_min_res_unit.

If you want more performance (as I had italicized), you can increase performance by increasing query_cache_min_res_unit. This will introduce more fragmentation and more query cache pruning.

YOUR ACTUAL QUESTION

  Is it correct interpretation that total_blocks / queries_in_cache = average number of blocks for each cached query (in my case 2.6)?

On average, Yes

  Would it make any sense to increase query_cache_min_res_unit from the default 4096?

Depends on what you want, more performance or less fragmentation.

With 23.12% fragmentation, I would work on decreasing query_cache_min_res_unit to lower the query cache's pruning activity against small queries. Strictly in terms of the query cache, performance will take care of itself. When you can drop the fragmentation percentage, you could possibly increase the query_cache_size to fit more query results IF AND ONLY IF the results are frequently accessed.

Think about this as well: each block is 1K since the Documentation says

  The query_cache_size value is aligned to the nearest 1024 byte block.

Thus, 2.6K per query result. Again, this gives you more reason to focus on reducing fragmentation.

UPDATE 2014-10-28 12:53 EDT

I just remembered something that I needed to correct. The size of a block is not 1K. I believe that the size of a query cache block is actually set by query_alloc_block_size. If this is the case, then 2.6 blocks is not 2.6K as I thought before.

Given the default value for query_alloc_block_size is 8K, then 2.6 blocks would be 20.8K.

In light of this, let's look at the numbers again.

TOTAL BLOCKS

18381 (number of total blocks) X 8192 (8K default block size) rounded up

mysql> select CEILING(18381 * 8192 / power(1024,2)) QueryCacheSizeInMB;
+--------------------+
| QueryCacheSizeInMB |
+--------------------+
|                144 |
+--------------------+

FREE BLOCKS

4250 (number of free blocks) X 8192 (8K default block size) rounded up

mysql> select CEILING(4250 * 8192 / power(1024,2)) QueryCacheFreeMB;
+------------------+
| QueryCacheFreeMB |
+------------------+
|               34 |
+------------------+

Simply subtraction shows that 110M is in use for the 7027 queries.

AVERAGE SPACE PER QUERY RESULT

mysql> select format(110 * power(1024,2) / 7027 / 1024,2) KBPerQueryResult;
+------------------+
| KBPerQueryResult |
+------------------+
| 16.03            |
+------------------+

That yields 16.03K per results.

BUT WAIT ...

Mathematically, we both figured out that 2.6 blocks per query was 20.8K.  Since average space per query result (16.03K) is a few bytes above 2.0 blocks, fragmentation is all but guaranteed.

SUGGESTION

The Documentation says of query_alloc_block_size

  If you have problems with memory fragmentation, it might help to increase this parameter.

Therefore, you should increase query_alloc_block_size to 16K, 24K or 32K. Try any multiple of 8K until the fragmentation is lower. Due this in conjunction with either increasing or decreasing query_cache_size.

GIVE IT A TRY !!!


SQL Server Service Pack Questions We are currently running our database server on SQL Server RTM; i.e. no service packs or cumulative updates have been installed.

We need to install the latest Service Pack for SQL Server.

I had the following questions based on this subject, I was told I needed to stand up new servers and transfer the databases to that server.

Will installation of a SQL Server service pack affect any of our production databases? We will do this in test first but still wanted to ask.
Do we need to restore the databases, after installation of a service pack? I will be doing a backup of the databases before we apply the SP, just want to know how it affects the database.
Are there any particular prerequisites we need to follow before the installation of service packs? Backup of course, but are there any others?
How can I determine if there are any known bugs in a given service pack? Should any Cumulative Updates be installed after the service pack?
Can I directly jump on to install SP3, instead of installing SP1 or SP2 first?

You do not need to stand up new servers to apply a SP.

  1 - Will installation of SQL Server service pack affect any of our
  production databases? We will do this in test 1st but still wanted to
  ask

It should be fine but always apply them to a test environment and let them soak for a while just to be safe.  Always a good idea to dig into the SP fixes and see what, exactly, they fixed.

  2 - Do we need to restore the databases, after installation of a service pack? I will be doing a backup of the DB's before we apply the SP, just want to know how it affects the DB.

No, you apply the SP during a maintenance window, it will stop the instance, patch it, then restart the instance for you.  Good idea to give the server a reboot if possible, that's personal preference though.  There is no need to backup/restore any DBs.  There will be a 5-10 minute outage while the instance is down.

  3 - Are there any particular prerequisites we need to follow before the installation of a service pack? Backup, of course, but are there any others?

I'd take a good backup of your DBs just to be completely safe.  Other than that, make sure your company knows that server will be unreachable for the time that you are patching.

  4 - How can I determine if there are any known bugs in a given service pack? Should any Cumulative Updates be followed after service pack installation?

https://sqlserverupdates.com/ is a great resource for getting the latest SP and CU.  As far as bugs, 2012 SP3 is very solid and I haven't heard of any major issues or deal breakers.

  5 - Can I directly jump on to install SP3, instead of installing SP1
  or SP2?

Yes, you can apply SP3, it encompasses all previous CUs and SPs.  Then you'll need to apply the most recent CU for SP3 (CU6, currently).


What's the difference between --read-only and just permit SELECT queries? I'm using Amazon Aurora and I have two endpoints, my endpoint which can read/write are having some issues to execute queries, these are the issues:

SQLSTATE[HY000]: General error: 1290 The MySQL server is running with the --read-only option so it cannot execute this statement, query was: UPDATE `integration` SET `setup_type` = ? WHERE (integration_id='4')

SQLSTATE[HY000]: General error: 1290 The MySQL server is running with the --read-only option so it cannot execute this statement, query was: UPDATE `indexer_state` SET `indexer_id` = ?, `status` = ?, `updated` = '2018-03-12 14:38:45', `hash_config` = ? WHERE (state_id='1')

Amazon uses the global --read-only configuration in one of the servers.
What's the difference between to use --read-only and a user which can just execute SELECT queries?

To the user, probably no difference.

To the system integrity, --read-only prevents you from accidentally writing to a Slave, thereby messing up the Replication integrity.

Aurora has no control (I assume) over whether you grant write permission and happen to let them hit a Slave with an UPDATE.  This is an easy, simple, prevention.


Searching for array elements nested in MongoDB Documents I need to store card documents in my database. Cards need to be stored with a set of integers that represent the bundle, box and pallet, or whatever other entity they belong to. The packaging structure needs to be of an arbitrary length to accommodate flexible packing structures (otherwise they would each have their own field in the document.)

I am considering a document structure like this, where the packaging numbers are kept as an array in the Packaging field.

{
"JobNumber" : "50001-01",
"CustomerId" : "joe",
"IdentifierNumber" : NumberLong(8812739),
"TimesPrinted" : 0,
"Packaging" : [
    1200,
    120,
    3
]
}

The Packaging field will be set once, then searched against many times.

I am wondering about performance because I will often need to query this collection for all documents whose nth element in the Packaging field is a given value. Is this something that MongoDB is good at? What are the indexes I should be considering?

Remember, MongoDB has a dynamic schema. So it is perfectly ok to store this document:

{
  "JobNumber" : "50001-01",
  "CustomerId" : "joe",
  "IdentifierNumber" : NumberLong(8812739),
  "TimesPrinted" : 0,
  "Packaging" : {"bundle":1200,"box":120,"pallet":3}
}

and this document

{
  "JobNumber" : "50001-02",
  "CustomerId" : "jane",
  "IdentifierNumber" : NumberLong(8812739),
  "TimesPrinted" : 0,
  "Packaging" : {"sack":200}
}

in the same collection.

Since, I wouldn't query for the Nth document, but for a given field in the subdocument, for example

 db.collection.find({"packaging.bundle":1200})

which would run just fine with MongoDB. The reason behind that is that if a field isn't present in a document, it is evaluated as null for a query. And null is definitely not equal to 1200.

As for the performance. It really depends on who big your collection is and how your queries look like. While the query as shown above may be rather slow in a collection containing hundred of thousands of documents (or even more) without an index, it can be extremely fast when you created an index on it, e.g.

    db.collection.ensureIndex({"packaging.bundle":1,"packaging.box":1,"packaging.pallet":1});

If you can create an index like this obviously depends on the question wether you really have arbitrary packaging or if you simply have a variety of packaging options. If the latter is the case, I'd create an index for each of the packaging options, utilizing sparse indices, e.g.

 db.collection.ensureIndex({"packaging.sack":1},{sparse:true})

This would reduce the index size, as only documents which hold the field "packaging.sack" would be contained in this index.

If you really have arbitrary fields in the documents, I wonder how you create a model for it ;)

When talking of just some ten thousands of documents, you might even get satisfying result without an index.


Logs growing and took up entire log drive even with log backups I have a volume just for logs and another volume just for log backups.

The log volume kept  growing until it to run out of space even with log backups taken every 1 minute.

Isn't the backups suppose to free up the space within the log file to be written over again?

It seems like this isn't happening.

run this query on your server

SELECT [NAME],
       RECOVERY_MODEL_DESC,
       LOG_REUSE_WAIT_DESC,
       IS_PUBLISHED,
       IS_MERGE_PUBLISHED,
       is_subscribed
FROM SYS.DATABASES

if you have something different than NOTHING in the LOG_REUSE_WAIT_DESC there is where I would start investigating.

There are eight reasons SQL Server might report when it cannot truncate the transaction log

That is probably what is preventing the space within the log to be re-used.

For ACTIVE_TRANSACTION

I like to use the stored procedure called sp_whoisactive it will tell you what is currently running on your server.

or much simpler, just to see if there are open transactions:

DBCC opentran WITH NO_INFOMSGS

For LOG_BACKUP

Why is log_reuse_wait_desc saying LOG_BACKUP after doing a log backup?

  LOG_BACKUP really means “either you need to take a log backup, or the
  log records that were backed up were all in the current VLF and so it
  could not be cleared.”

There are eight reasons SQL Server might report when it cannot truncate the transaction log

  If your database recovery model is "full" SQL Server cannot reuse
  virtual log files unless all contained transaction log records have
  been backed up with a log backup. If a log backup is outstanding the
  log has to grow to accommodate new data changes. During this time you
  will see a log_reuse_wait_desc of LOG_BACKUP. Full or differential
  backups do not backup all transaction log records, so you need to run
  an actual log backup to allow for virtual log file reuse.

In this situation transaction log backups every minute I think it too high.

Find the number of VLFs in the transaction log.

I had this situation:
MY_DB (232 Gb) = 500,000 VLFs

and this is what I did (keeping each VLF = 8GB):

dbcc sqlperf(logspace)
go
use my_db
Go
select size/128,* from sys.sysfiles
dbcc loginfo('my_db')

-- updated my_db autogrowth to 4096Mb (chunk of 4 Gb)
DBCC SHRINKFILE(my_db_log, 1)
select size/128,* from sys.sysfiles

ALTER DATABASE [MY_DB] MODIFY FILE ( NAME = N'my_db_log', SIZE = 8GB )
ALTER DATABASE [MY_DB] MODIFY FILE ( NAME = N'my_db_log', SIZE = 16GB )
ALTER DATABASE [MY_DB] MODIFY FILE ( NAME = N'my_db_log', SIZE = 24GB )
ALTER DATABASE [MY_DB] MODIFY FILE ( NAME = N'my_db_log', SIZE = 32GB )
ALTER DATABASE [MY_DB] MODIFY FILE ( NAME = N'my_db_log', SIZE = 40GB )
ALTER DATABASE [MY_DB] MODIFY FILE ( NAME = N'my_db_log', SIZE = 48GB )
ALTER DATABASE [MY_DB] MODIFY FILE ( NAME = N'my_db_log', SIZE = 56GB )
ALTER DATABASE [MY_DB] MODIFY FILE ( NAME = N'my_db_log', SIZE = 64GB )
ALTER DATABASE [MY_DB] MODIFY FILE ( NAME = N'my_db_log', SIZE = 72GB )
ALTER DATABASE [MY_DB] MODIFY FILE ( NAME = N'my_db_log', SIZE = 80GB )
ALTER DATABASE [MY_DB] MODIFY FILE ( NAME = N'my_db_log', SIZE = 96GB )
ALTER DATABASE [MY_DB] MODIFY FILE ( NAME = N'my_db_log', SIZE = 104GB )
ALTER DATABASE [MY_DB] MODIFY FILE ( NAME = N'my_db_log', SIZE = 112GB )
ALTER DATABASE [MY_DB] MODIFY FILE ( NAME = N'my_db_log', SIZE = 120GB )
ALTER DATABASE [MY_DB] MODIFY FILE ( NAME = N'my_db_log', SIZE = 128GB )
ALTER DATABASE [MY_DB] MODIFY FILE ( NAME = N'my_db_log', SIZE = 136GB )
ALTER DATABASE [MY_DB] MODIFY FILE ( NAME = N'my_db_log', SIZE = 144GB )
ALTER DATABASE [MY_DB] MODIFY FILE ( NAME = N'my_db_log', SIZE = 152GB )
ALTER DATABASE [MY_DB] MODIFY FILE ( NAME = N'my_db_log', SIZE = 160GB )
ALTER DATABASE [MY_DB] MODIFY FILE ( NAME = N'my_db_log', SIZE = 168GB )
ALTER DATABASE [MY_DB] MODIFY FILE ( NAME = N'my_db_log', SIZE = 176GB )
ALTER DATABASE [MY_DB] MODIFY FILE ( NAME = N'my_db_log', SIZE = 184GB )
ALTER DATABASE [MY_DB] MODIFY FILE ( NAME = N'my_db_log', SIZE = 192GB )
ALTER DATABASE [MY_DB] MODIFY FILE ( NAME = N'my_db_log', SIZE = 200GB )
ALTER DATABASE [MY_DB] MODIFY FILE ( NAME = N'my_db_log', SIZE = 208GB )
ALTER DATABASE [MY_DB] MODIFY FILE ( NAME = N'my_db_log', SIZE = 216GB )
ALTER DATABASE [MY_DB] MODIFY FILE ( NAME = N'my_db_log', SIZE = 224GB )
ALTER DATABASE [MY_DB] MODIFY FILE ( NAME = N'my_db_log', SIZE = 232GB )
ALTER DATABASE [MY_DB] MODIFY FILE ( NAME = N'my_db_log', SIZE = 240GB )

Transaction Log VLCs - each is a multiple of 4GB


Daylight Saving time In my environment, there are servers running on native backup and Ola Hallengren plans. Our servers are a combination of 2008, 2012 and 2014. All the full backups are taken at 12am and log backups are taken every 15 minutes.

I have never accounted for Daylight Saving time before, so please tell me what adjustments I should make.

Will the 12am full backups be affected and what happens to the log backups?

SQL Server expert Paul Randal addresses this very topic in How does daylight savings time affect disaster recovery?.  It's a relatively short post, so I'm quoting it in its entirety.  Since you were concerned about your transaction log backups, I also highlighed a bit of the post to draw that to your attention.

  It’s common knowledge that SQL Server copes with daylight savings time
  (DST) correctly so why should you care?

  Well, it’s not so common knowledge that at the end of DST when the
  clocks go back an hour (always at 02:00 in the U.S.), SQL Agent
  essentially pauses for an hour (in at least SS2000 onwards). This
  means that if you have a job that’s doing something every 15 minutes,
  there will be a gap of 75 minutes between the job execution at 01:45
  and the job execution at 02:00. This happens because at 02:00, the
  time is set back to 01:00 but the next run time of all the jobs
  remains the same – so your job cannot execute until it’s next
  scheduled time of 02:00. So, in the northern hemisphere every Fall,
  and in the southern hemisphere every Spring, you lose an hour’s worth
  of SQL Agent jobs. Still, why should you care?

  Well, it depends what the jobs are that get delayed by an hour. If you
  have a job that takes a log backup every 15 mins then on the day DST
  ends, there’s actually a gap of 75 minutes between log backups. If you
  have a Service Level Agreement (SLA) that limits the maximum amount of
  lost work to 15 minutes in the event of a disaster, then for those 75
  minutes you’re exposed to potentially not being able to meet that SLA!

  That could be a pretty big deal, especially if something goes wrong
  during that hour (no more or less likely than something going wrong at
  any other time, but still possible). In that case, you need to come up
  with an alternative solution. A couple of ways to get around the
  problem I can think of:

Have someone stay up late during that hour and take manual log
backups.
Switch over to database mirroring, which continually streams the log
to the redundant server and so isn’t affected the DST issue.

  Both of these are viable solutions but I think the best one is to
  create a SQL Agent job that runs at 01:59 and creates extra backup
  jobs to run at 01:00, 01:15, 01:30, and 01:45. I don’t see why this
  shouldn’t be possible. At 10:36 this morning I created a simple agent
  job to print the date to a file and set it to execute at 09:40 – in
  the past. I then set my system time back one hour and the job executed
  perfectly. The only downside of this solution is that you need to
  create and schedule the extra jobs using the T-SQL Agent SPs embedded
  in job steps for your 01:59 job – tedious but not hard. Maybe someone
  could send me a script and I’ll blog it as a follow-on?

  So with DST coming to an end on November 4th this is definitely
  something for you to be aware of even if you don’t want to go to the
  trouble of coping with the extra hour’s exposure. As an aside – the
  dates when DST starts and ends changed this year. KB article 931975
  discusses which parts of SQL Server aren’t aware of the changed dates
  and what you can do about it.


4 tables, find department with the highest average salary ABC is an organization operating in a certain country. Below are the data for the database that manages the information on this organization's employees:

employee(employee_name, street, city)

works(employee_name, department_name, position, salary)

department(department_name, city)

manages(employee_name, manager_name).

I have 10 different select queries about this database, and I've solved 8 of them, but am stuck on solving 2.

The first query is 'Select the names of the employees that live in the same street and city as their managers'.

The second query is 'select the department with the highest average salary'.

Any help is appreciated, if any more details are necessary, please tell me.

First query:

select
    e.employee_name as employee
    ,e.street
    ,e.city
    ,mgr.employee_name as manager
    ,mgr.street
    ,mgr.city
from
    employee e
    join manages mgs on mgs.employee_name = e.employee_name
    join employee mgr on mgr.employee_name = mgs.manager_name
where
    e.street = mgr.street
    and e.city = mgr.city

Second query:

select
    d.department_name
    ,avg(salary) as AvgSalary
from
   department d
   join works w on w.department_name = d.department_name
group by
   d.department_name
order by
   AvgSalary desc


Difference between two tables I am trying to pull the differences between two tables, each in a different database.  I thought I could just do a full outer join but I am getting back too many results.

The T-SQL below returns 167 results

SELECT N'{'+ADobjectGUID+N'}' [GUID], first_name [First], last_name [Last], description [Full] FROM [db1].[dbo].[tb1] where delete_date is null
except
SELECT ObjectGUID [GUID], GivenName [First], sn [Last], displayName [Full] FROM [db2].[dbo].[tb1]

When I reverse it to be the below, it returns 214 results

SELECT ObjectGUID [GUID], GivenName [First], sn [Last], displayName [Full] FROM [db2].[dbo].[tb1]
except
SELECT N'{'+ADobjectGUID+N'}' [GUID], first_name [First], last_name [Last], description [Full] FROM [db1].[dbo].[tb1] where delete_date is null

However, I need more than just those columns from the databases, so I can't use the except statement.

select N'{'+ADobjectGUID+N'}' [GUID], first_name [First], last_name [Last], db1.dbo.tb1.description [Full], domain_user_id, sAMAccountName, employeeID
from db1.dbo.tb1
full outer join [db2].[dbo].[tb1]
on ObjectGUID = N'{'+ADobjectGUID+N'}'
    and first_name = GivenName
    and last_name = sn
    and tb1.description = displayName
where db1.dbo.tb1.delete_date is null

That query results in 1784 results.  Based on the above, I should be getting 371 results.  What am I missing.  I am still new to SQL and am sure it is something small.

FULL joins can be tricky. Try this version:

select
    -- columns from db1
    a_GUID = a.ObjectGUID,
    a_First = a.first_name,
    a_Last = a.last_name,
    a_Full = a.description,
    a.domain_user_id,
    a.sAMAccountName,
    a.employeeID,

    -- columns from db2
    b_GUID = N'{' + b.ADobjectGUID + N'}',
    b_First = b.GivenName,
    b_Last = b.sn,
    b_Full = b.displayName,
from
    ( select *
      from db1.dbo.tb1 as a
      where a.delete_date is null
    ) as a
  full outer join
    db2.dbo.tb1 as b
  on
      a.ObjectGUID = N'{' + b.ADobjectGUID + N'}'
  and a.first_name = b.GivenName
  and a.last_name = b.sn
  and a.description = b.displayName
where
      a.ObjectGUID is null
   or b.ADobjectGUID is null
  ;


MySQL users not visible after import I have a Percona MySQL database 5.6 running in a linux machine. I have created new users called test1 and test2. What I did was I imported mysql database from my test setup to this new setup. After which when I run the query

SELECT CONCAT('SHOW GRANTS FOR ''',user,'''@''',host,''';') FROM mysql.user;

I'm not able to see the new users which I created before the import was done but I'm able to connect to MySQL with the users I created before import.

Even after import my new users exist then it has to show in the mysql database right? What is wrong?

Thanks!!

Best is to to : show grants for user@'10.%' ;  each user on the original server.
Then apply them to the new.

Or you can use pt-show-grants -hhostname -u -p which will list all the grants.
You can do that in one line of code:

pt-show-grants -horigalHost -u -p | mysql -hnewHost -u -p


Strategy for regularly updating datasets from excel files I have ~10 excel files which are produced by a third party and updated each night. I've have a script that downloads them to my local machine. They contain ~ 10 fields (all short text / dates) and between ~10,000 and ~1m rows in each.

I'm planning to create a simple web application to enable people to search the data. I'll host it on AWS or similar. Search load will be light maybe ~1000 searches / day.

I have to assume that all the records are unique each night and need completely replace the online dataset.

It's relatively simple for me to convert the data from the excel files into a database such as Postgres and create a simple search on top of it.

My question is how do I deal with the time it takes to do the database update each night? Should I create two databases and have my application alternate between them every other night?

What is a typical strategy for dealing with a situation like this?

My current skill set is Ruby/Rails/Postgres building and simple web apps. But I've been intentionally vague about technology because I'm open minded about what to use. And I'm quite happy to learn something new to solve the problem.

One method is to have two sets of tables (This1, This2, That1, That2, etc.), or a set of tables in separate databases/schemas. Either way, you now have SetA and SetB. Have a set of views that access one set of tables, say SetA. Perform the nightly imports to SetB. When the new data is ready to go, recreate the views, pointing them now to the SetB tables. The next night, use SetA to import.

The views will act as a software switch with an unnoticeable switch-over rate. And it should be dead simple to add this to existing Ruby script to coordinate all of it.


Postgresql extremely slow count (with index, simple query) I need to run these simple queries on a table with millions of rows:

SELECT COUNT(*) FROM "subscriptions" WHERE "subscriptions"."project_id" = 123;

SELECT COUNT(*) FROM "subscriptions" WHERE "subscriptions"."project_id" = 123 AND "subscriptions"."trashed_at" IS NULL;

The count result for both queries, for project 123, is about 5M.

I have an index in place on project_id, and also another index on (project_id, trashed_at):

"index_subscriptions_on_project_id_and_created_at" btree (project_id, created_at DESC)
"index_subscriptions_on_project_id_and_trashed_at" btree (project_id, trashed_at DESC)

The problem is that both queries are extremely slow and take about 17s each.

These are the results of EXPLAIN ANALIZE:

      QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=2068127.29..2068127.30 rows=1 width=0) (actual time=17342.420..17342.420 rows=1 loops=1)
   ->  Bitmap Heap Scan on subscriptions  (cost=199573.94..2055635.23 rows=4996823 width=0) (actual time=1666.409..16855.610 rows=4994254 loops=1)
         Recheck Cond: (project_id = 123)
         Rows Removed by Index Recheck: 23746378
         Heap Blocks: exact=131205 lossy=1480411
         ->  Bitmap Index Scan on index_subscriptions_on_project_id_and_trashed_at  (cost=0.00..198324.74 rows=4996823 width=0) (actual time=1582.717..1582.717 rows=4994877 loops=1)
               Index Cond: (project_id = 123)
 Planning time: 0.090 ms
 Execution time: 17344.182 ms
(9 rows)

      QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=2047881.69..2047881.70 rows=1 width=0) (actual time=17557.218..17557.218 rows=1 loops=1)
   ->  Bitmap Heap Scan on subscriptions  (cost=187953.70..2036810.19 rows=4428599 width=0) (actual time=1644.966..17078.378 rows=4994130 loops=1)
         Recheck Cond: ((project_id = 123) AND (trashed_at IS NULL))
         Rows Removed by Index Recheck: 23746273
         Heap Blocks: exact=131144 lossy=1480409
         ->  Bitmap Index Scan on index_subscriptions_on_project_id_and_trashed_at  (cost=0.00..186846.55 rows=4428599 width=0) (actual time=1566.163..1566.163 rows=4994749 loops=1)
               Index Cond: ((project_id = 123) AND (trashed_at IS NULL))
 Planning time: 0.084 ms
 Execution time: 17558.522 ms
(9 rows)

What is the problem?

What can I do to improve the performance (i.e. count in a few seconds)?

Fetching and counting 5 million rows is slow business.

There are two problems:

The bitmap heap scan is taking longer than necessary, because work_mem is so small that it cannot contain a bitmap with one bit per table row.

It then degrades to storing a bit per 8KB block, which leads to more heap fetches in the bitmap heap scan phase to sort out the false positives.

So raising work_mem will speed up the execution, but according to your comments not a lot.
Rather than consulting the table itself, which is slow, PostgreSQL could also read only one of the indexes, because they contain all the required data.

That would be much faster, so why does PostgreSQL not do that?

The explanation is that PostgreSQL tables and indexes contain row versions (tuples) that are visible and tuples that are not visible (because they have been deleted or updated and not vacuumed yet). These “dead tuples” are required for PostgreSQL multi-versioning implementation.

Now the information which tuples are visible and which are not is only stored in the table (the heap), not in the index. So PostgreSQL has to consult the heap to figure out which tuples to count and which not.

If you run VACUUM on the table, PostgreSQL will update a nice thing called the “visibility map” that contains one bit per 8KB block that indicates if all tuples in the block are visible to everyone or not.

If the visibility map has a block as all-visible, the index scan doesn't have to consult the table to determine if it can see a tuple in the block or not. If that works for most tuples, you get an index only scan which is obviously much faster.

So you should see that the table is vacuumed often enough.

For that, make autovacuum run faster and more often on this table:

ALTER TABLE subscriptions SET (
   autovacuum_vacuum_scale_factor = 0.05,
   autovacuum_vacuum_cost_delay = 2
);

You would be even faster if you had an index on only project_id.


High autovacuum CPU/IO usage from database with more than 10k append-only tables In my system I have more than 10k tables that are very similar, I only do insert operations in these tables and delete old data via a cron schedule one time per day.
These insertions come each minute, a mean of 5 rows per table which are inserted in a single insert query, meaning something around 50k rows per minute.
The issue I'm seeing is that stats collector (I disabled all stats except track_counts) uses a lot of CPU and IO all the time.
The same happens to autovacuum which will also use a lot of the PC resources for itself.
Another issue is that they never seem to stop, autovacuum and stats collector are always running as long as I'm always adding new data in the rate shown above.
If I disable track_counts and autovacuum, it fixes my issue entirelly, the database is kept idle most of the time.
The problem with this approach is that I obviously are not running vacuum anymore.
So, my question is, would it be a good solution for my case to disable autovacuum entirelly and run VACUUM FULL after my cron script deleted old data? This would mean that I would trigger a full vacuum query for each table one time per day.
If not, do you have some sugestions for postgres configuration changes to improve the situation without disabling autovacuum?
This is part of my postgresql.conf (feel free to ask for more information about it if needed):
track_activities = off
track_counts = on
track_io_timing = off
track_functions = none

log_parser_stats = off
log_planner_stats = off
log_executor_stats = off
log_statement_stats = off

#------------------------------------------------------------------------------
# AUTOVACUUM
#------------------------------------------------------------------------------

autovacuum = on

log_autovacuum_min_duration = -1

autovacuum_max_workers = 6
autovacuum_naptime = 3600

I already saw some answers for cases of append-only tables or big tables, but I think I'm seeing this issue because in my case I have a LOT of tables.
Also, for completeness, I'm running the database in Postgres 12 and with extension TimescaleDB 1.7.1 inside docker.

Disabling autovacuum is a very bad idea. Autovacuum removes dead lines and updates statistics. Disabling autovacuum will quickly lead to performance loss. Also, when autovacuum is disabled, sooner or later you will encounter a wraparound problem.
Since you are using TimescaleDB, why not use the drop_chunks() function to remove old data? This will significantly reduce the need to perform vacuum on tables.
However, autovacuum will still need to handle tables to prevent the wraparound I wrote about above. In addition, you can change the size of the chunk so that the size of the data to be processed by autovacuum is not so large.
There are no universal values for the autovacuum setting. Try adjusting the following settings for the best performance in your case:
autovacuum_vacuum_threshold
autovacuum_vacuum_scale_factor
autovacuum_vacuum_cost_delay

For statistics:
autovacuum_analyze_threshold
autovacuum_analyze_scale_factor

You can also configure cost parameters for the autovacuum daemon to process pages:
vacuum_cost_page_hit
vacuum_cost_page_miss
vacuum_cost_page_dirty
vacuum_cost_limit