DTA Recommends to CREATE STATISTICS I just ran a T-SQL query through DTA and one of the recommendations is to CREATE STATISTICS on one of the columns that is part of many of the queries in the SQL code file.

My question is, how exactly do statistics aid in performance?

statistics is used when query execution plan created.

  Statistics for query optimization are objects that contain statistical information about the distribution of values in one or more columns of a table or indexed view. The query optimizer uses these statistics to estimate the cardinality, or number of rows, in the query result. These cardinality estimates enable the query optimizer to create a high-quality query plan. For example, the query optimizer could use cardinality estimates to choose the index seek operator instead of the more resource-intensive index scan operator, and in doing so improve query performance.

See http://msdn.microsoft.com/en-us/library/ms190397.aspx for more details.


Pass tablename as sql script argument I'm trying to automatize a bunch of postgreSQL process, namely to use PostGIS and pgRouting extensions, so for that purpose I created a sql script file that I could run on command line passing in an input argument, a table name, like the following:

ALTER TABLE ''''||:table::char(52)||''''  ADD COLUMN "source" integer;
ALTER TABLE ''''||:table::char(52)||'''' ADD COLUMN "target" integer;

SELECT pgr_createTopology(''''||:table::char(52)||'''', 0.00001, 'geom', 'gid');
CREATE INDEX ''''||:table::char(52)||''''_source_idx ON ''''||:table::char(52)||''''("source");
CREATE INDEX ''''||:table::char(52)||''''_target_idx ON ''''||:table::char(52)||''''("target");

So when I try to run in the command line, as such:
psql -h my_host -U my_user -d my_database -c table="my_table" -f /script/pgRouting.sql

I get a syntax error, so what's the correct way of doing this?

You may use the psql variable table like this:

ALTER TABLE :table ADD COLUMN...

psql will interpolate :table and send this query:

  ALTER TABLE realtablename ADD COLUMN...

to the SQL interpreter,
when it's called with -v table=realtablename

There's a pre-condition, though, that the table's name doesn't require quoting.

The SELECT may be written as:

SELECT pgr_createTopology(:'table', 0.00001, 'geom', 'gid');

then :table will be interpolated to a literal string, so the query actually produced would be like:

SELECT pgr_createTopology('realtablename', 0.00001, 'geom', 'gid');

However, this technique can't really be used for more complicated things like the CREATE INDEX mentioned, as it doesn't need the variable's value but another value based on it.

Personally for that sort of thing, I would embed the SQL script inside a shell script and let the shell do the interpolating. Example:

#!/bin/bash
# ...
tablename="$1"
psql [options] <<EOF
ALTER TABLE ${tablename} ADD COLUMN "source" integer;
-- ...
CREATE INDEX ${tablename}_source_idx ON $tablename("source");

EOF

The nice thing with the shell is that it has no problem concatenating with ${tablename}_source_idx ,whereas psql will not accept :"tablename"_source_idx or (as far as I know) offer a simple alternative.

Anyway, if $tablename can contain problematic characters such as separators or quotes, either double or single or both, none of the above is  good enough. As your SQL statements need the table's name both as a literal string and as an identifier, the problem is not as easy as it would seem.

The ultimate solution, to cover for any syntactic hazard in the table's name, could be to define and call a temporary plpgsql function that uses dynamic SQL functionalities.

/* inside SQL script */
CREATE FUNCTION pg_temp.create_stuff(tablename text) returns void AS $$
BEGIN
  EXECUTE format('ALTER TABLE %I ADD COLUMN source INTEGER', tablename);
  EXECUTE format('ALTER TABLE %I ADD COLUMN target INTEGER', tablename);

  --
  EXECUTE format('SELECT pgr_createTopology(%L, 0.00001, ''geom'', ''gid'')',
    tablename);
  --
  EXECUTE format('CREATE INDEX %I ON %I(source)',
   tablename||'_source_idx', tablename);
  EXECUTE format('CREATE INDEX %I ON %I(target)',
   tablename||'_target_idx', tablename);
END
$$ language plpgsql;

/* still inside SQL script, call function with table as string literal */
select pg_temp.create_stuff(:'table');

I don't suggest a DO block because they don't take input parameters.


Best Format to Bulk INSERT/UPDATE Data to SQL SERVER using C# Can anyone tell me what is best way to bulk insert/update data to SQL SERVER 2012 using C#? I have an excel file which I am passing as TVP(table value parameter) to a SP. But this is little bit slower. Any hint will appreciated.

What about "NO FORMAT"? Use SqlBulkCopy to dump the data into a temp table. No need to put a file in a place the server can read at all.


Does my PostgreSQL instance support kerberos/gssapi authentication? I installed it following instructions from digital ocean how-to-install-postgresql-on-ubuntu-20-04-quickstart:
% sudo apt install postgresql postgresql-contrib

I also did a 'show all' in psql and didn't see anything that suggested it was other than the krb_server_keyfile = /etc/krb5.keytab that I configured in postgresql.conf
These are the errors I see in the log:
2021-05-07 21:58:49.990 UTC [1434] postgres@postgres LOG:  accepting GSS security context failed
2021-05-07 21:58:49.990 UTC [1434] postgres@postgres DETAIL:  Unspecified GSS failure.  Minor code may provide more information: Key table entry not found
2021-05-07 21:58:49.990 UTC [1434] postgres@postgres FATAL:  GSSAPI authentication failed for user "postgres"
2021-05-07 21:58:49.990 UTC [1434] postgres@postgres DETAIL:  Connection matched pg_hba.conf line 114: "host postgres     postgres           172.31.93.176/24                              gs\
s include_realm=0"
2021-05-07 21:58:49.993 UTC [1435] postgres@postgres LOG:  accepting GSS security context failed
2021-05-07 21:58:49.993 UTC [1435] postgres@postgres DETAIL:  Unspecified GSS failure.  Minor code may provide more information: Key table entry not found
2021-05-07 21:58:49.993 UTC [1435] postgres@postgres FATAL:  GSSAPI authentication failed for user "postgres"
2021-05-07 21:58:49.993 UTC [1435] postgres@postgres DETAIL:  Connection matched pg_hba.conf line 114: "host postgres     postgres           172.31.93.176/24                              gs\
s include_realm=0"

First, let me add some more description of the environment where this problem was observed.
This excellent series of articles describes setting up kerberos and postgresql
PostgreSQL GSSAPI Authentication with Kerberos

Part-1
Part-2
Part-3

If you do this in AWS, you should also setup elastic-ip addresses (so you can stop and start your servers). I also setup rt53 for the host names and have those map to the elastic-ip addresses you setup.
Note: in my case I am using postgresql v13 and after my initial failure, I followed these instructions on the postgressql for installing postgresql.
You would think that would be enough. However, if you don't tweak the /etc/hosts file so that you have entries for your servers (as David did in his example, that was on 'in-house' hardware) you get this error:
postgres@jl-krb5dev-client:~$ psql -U postgres -d postgres -h
jl-krb5dev-postgres.matlabonlineserver.com psql: error: could not
initiate GSSAPI security context: Unspecified GSS failure.  Minor code
may provide more information: Server
krbtgt/COMPUTE-1.AMAZONAWS.COM@MATLABONLINESERVER.COM not found in
Kerberos database FATAL:  no pg_hba.conf entry for host "52.70.13.95",
user "postgres", database "postgres", SSL on FATAL:  no pg_hba.conf
entry for host "52.70.13.95", user "postgres", database "postgres",
SSL off

But if you add entries in the /etc/hosts file on your client machine for the kerberos and postgresql machines (using the elastic-ip addresses you setup earlier) everything works fine.
Note: this does require that you properly configured postgres with a keytab file.
For v13 of postgresql, I put it in /etc/postgresql/13/main/conf.d/krb5.keytab AND adjusted the postgresql.conf file (in /etc/postgresql/13/main) so that it has an entry like this: krb_server_keyfile = 'FILE:/etc/postgresql/13/main/conf.d/krb5.keytab
A Big Thanks to @jjanes for getting me on the right path in the comment to my initial question.


Writing data to SSISDB.Catalog.Executions I am trying to test a stored procedure which reads some values from SSISDB.Catalog.Executions. For achieving the same goal I want to write dummy data to SSISDB.Catalog.Executions. Is that even possible? If yes, how?

The question asks about inserting into catalog.executions. The entire catalog schema is just views over the internal schema tables which have security role checks in place.

If you look at the definition for catalog.create_execution after doing some permission checks, it will use an CLR proc to insert into internal.operations to generate an ID and then the actual insertion into internal.executions

-- Parameters
DECLARE
    @folder_name nvarchar(128) = (SELECT TOP 1 F.name FROM internal.folders AS F ORDER BY 1)
,   @project_name nvarchar(128) = (SELECT TOP 1 P.name FROM internal.folders AS F INNER JOIN internal.projects AS P ON P.folder_id = F.folder_id ORDER BY F.name)
,   @package_name nvarchar(260) = (SELECT TOP 1 PKG.name FROM internal.folders AS F INNER JOIN internal.projects AS P ON P.folder_id = F.folder_id INNER JOIN internal.packages AS PKG ON PKG.project_id = P.project_id ORDER BY F.name)
,   @reference_id bigint = NULL
,   @use32bitruntime bit = 0
,   @execution_id bigint;

DECLARE @caller_name internal.adt_sname = (SELECT USER_NAME());
DECLARE @caller_sid internal.adt_sid = (SELECT USER_ID());
DECLARE @created_time datetimeoffset;
DECLARE @return_value int;
DECLARE @operation_id bigint;
DECLARE @result bit;
DECLARE @environment_id bigint;
DECLARE @environment_found bit;
DECLARE @environment_name nvarchar(128)
DECLARE @environment_folder_name nvarchar(128)
DECLARE @reference_type char(1)
DECLARE @version_id bigint

SET @created_time = SYSDATETIMEOFFSET();
SET @operation_id = -1;

INSERT INTO
internal.operations
(
    operation_type
,   created_time
,   object_type
,   object_id
,   object_name
,   status
,   start_time
,   end_time
,   caller_sid
,   caller_name
,   process_id
,   stopped_by_sid
,   stopped_by_name
,   operation_guid
,   server_name
,   machine_name
)
VALUES
(
    200
,   SYSDATETIMEOFFSET()  -- created_time - datetimeoffset
,   20
,   0
,   N'Demo'  -- object_name - nvarchar(260)
,   1  -- status - int
,   SYSDATETIMEOFFSET()  -- start_time - datetimeoffset
,   SYSDATETIMEOFFSET()  -- end_time - datetimeoffset
,   @caller_sid
,   @caller_name
,   0  -- process_id - int
,   NULL  -- stopped_by_sid - varbinary(85)
,   N''  -- stopped_by_name - nvarchar(128)
,   NULL  -- operation_guid - uniqueidentifier
,   @@SERVERNAME
,   @@SERVERNAME
)

SELECT
    @operation_id = SCOPE_IDENTITY();

INSERT into internal.executions
(
    execution_id
,   folder_name
,   project_name
,   package_name
,   reference_id
,   reference_type
,   environment_folder_name
,   environment_name
,   project_lsn
,   executed_as_sid
,   executed_as_name
,   use32bitruntime
)
VALUES
(
    @operation_id
,   @folder_name
,   @project_name
,   @package_name
,   @reference_id
,   @reference_type
,   @environment_folder_name
,   @environment_name
,   @version_id
,   @caller_sid
,   @caller_name
,   @use32bitruntime
);

-- Look at what I've done
SELECT @operation_id AS OperationId;


SQL Server Grant CRUD to Users on One Schema I have a schema called training. I want users who will be trained to have CRUD permissions on any object on that schema. I do not want them to have any other permissions on any other object outside that schema, and I also do not want them to have create/drop permissions for the schema itself. What is the least permissions to grant this type of access?

Currently, I set up the schema training with dbo as the owner. Then I created a role called training_modify and added the training schema as a securable with "Select", "Update", "Insert", and "Delete" explicitly granted. Finally, I will add users to that role.

Is this the right approach? Does it have any unintended consequences?

Is this the right approach?

This is the correct approach, users with that role will only be able to perform CRUD operations on objects in the training schema and nothing else. This is the least privilege approach and is correct.

True least privilege would actually be assigning permissions to only the specific objects in that schema, but as you've indicated users should have access to all objects, then applying permissions schema-wide is appropriate.

  I do not want them to have any other permissions on any other object
  outside that schema

You may not actually want this. Consider the public server role. For any object that does not have explicit permissions (GRANT or DENY), permissions are inherited from the public role for a user (any user, all users).

Since you cannot revoke public membership from a user, you would need to explicitly DENY permission on every single object just to prevent users from seeing the object listed in Management Studio - is this really necessary? They have no access beyond seeing the object exists, users still cannot interact with them.

Public access grants users, by default, nothing more than access to a few system catalog views to allow all users to view the name and definition of basic objects and some basic DB/Server info (see this article for more info).


Corruption in D:\$MFT. How may this progress? I don't think CHECKDB is useful here? I have a server that is reporting NTFS errors; one of the error messages implicates d:\$MFT as being possibly corrupt. My SQL data files reside on this drive.

The Master File Table holds information about all files on the disk, so I imagine if the corruption was to occur SQL may not be able to read data from one of the MDFs or NDFs?

I imagine CHECKDB will not be a relevant consistency check, and the only way to resolve this error would be a chkdsk /r?

Any help would be appreciated. We haven't had any corruption reports in the SQL error log yet. I imagine it could get serious depending on what parts of the MFT are corrupted (as previously stated it could affect SQL's ability to read MDFs or NDFs)?

Thanks

Here is how I would approach that issue.

Take care of SQL Databases.

Full backup of databases
Restore databases to development/test server
Run CHECKDB on development/test server on restored databases to check for errors.

If errors are encountered when running CHECKDB then you should address those issues, it means corruption happened somewhere in the line.  Seperate topic for recovering for this.

Take care of drive on server

stop SQL Server process.
run CHECKDSK /r on drive
Hope for best.  There are stress test utilities out there that can tell you if the drive is having problems.  If this is a single drive, then consider replacing it.  If part of a raid group, find a health check utility from the manufacturer and see if that tells you anything.

If CHECKDSK /r fixed the issue with the Master File Table then you should run CHECKDB on the databases again to make sure there isn't any corruption (or any corruption introduced into the files by CHECKDSK /r.

Side Notes:

Do you have PAGE VERIFY by CHECKSUM set for each of the databases?
When you do backups, do you use the CHECKSUM option?


Doing something immediately with data in array field I have a table that looks like this:

stuff
    id
    content
    score
    children[]

I'd like to run a query that selects all the children for a given id, and then right away gets the full records for all these children sorted by score. The children field holds an array of ids.

I need to do something like this:

SELECT children as array
FROM stuff
where id=1

then

SELECT * FROM stuff
WHERE id=(any id from previous select)
SORT BY score DESC

Children has an array of ids that reference the same table, just like a person (id) can have children (an array of people). I want the full records for all the kids of a single parent, sorted by score.

Any suggestions on the best way to do this? I've looked into WITH RECURSIVE but I'm not sure that's workable.

I have to admit, I'm not sure what you're asking for. You've left out important items in your schema, like what are the "full records" you wish to pull?

If you have control over the schema, perhaps you would be better off altering it. It seems like your trying to store test scores, but store an array of children for each score received. Now it may be just me, but perhaps you might be better off splitting this into multiple tables.

stuff
  stuff-id
  content

children
  child-id
  child-fname
  child-lname
  child-seat
  child-wild
  gum-chewer-bit

scores
  child-id
  stuff-id
  score

In this setup you can construct a query that matches records on the stuff-id and uses a join to pull data from the other tables.

If you DON'T have access to the schema, it may still be possible using IN (). Without having a clearer understanding of the schema my suggestion is to use join queries while passing that array to IN. That being said, I'm not sure it could work that way as I'm not sure you can construct the join in this fashion (hence the multiple tables and not using an array field).

Now with a stored procedure, the answer is a definite "yes, you can".

You would loop through each stuff-id, split out the array values (or pass them directly/indirectly with IN), loop through the child records, and flatten it out into a single record per child. With stored procedures you have quite a bit of control over each record you "suspend" (old Firebird dude here) with 'Returns Sets'.

To sort by score, you would achieve this by sorting the output of your stored procedure.

Don't give up either. Postgresql is really amazing in what you can do, and I recommend camping in their online manuals.


Is it faster to truncate destination and copy all source data or lookup and keep only non-matching data with SSIS transfer packages? I am building an Azure SQL DB database and to do this I will have to regularly (weekly-monthly) pull data from various other servers. I am designing SSIS packages to do this and whilst most of the data import tasks run within an hour, a few take much longer (it's been running all day and hasn't finished yet) due to the much larger number of rows in these tables. Optimally, the transfers would run as quickly as possible to minimise the costs of running our Azure SSIS IR.

I have so far been transferring the data by truncating each destination table (to prevent duplication) and then copying over the entire contents of the source table (as I just want to replicate it).

Would it (in general, assuming the majority of rows in each table are not new this week/month) be faster to transfer data in this way or would it be preferable to use a lookup task and copy only non-matching (i.e. new) data from the source database to the destination, rather than truncating the table and refilling it completely each time? (Please correct me if there's a more appropriate way of doing this or I've misunderstood how lookup works!)

I can't currently test which is faster as the initial transfer is still going, so the tables aren't fully populated yet, and also I'm new to SSIS so would really appreciate some advice from people more familiar with it. For context, I only have read-only access to the source servers and not all source tables have unique row IDs, so matching on row ID alone or changing the source tables are not strategies I can use.

Is it faster...

It depends on a great many things so there is no single correct straight answer to this:

What proportion of the target rows are you needing to delete, update or insert anew, if you don't use the truncate+replace method
The performance metrics of the source and target systems
The network performance between where your SSIS packages are running and the source and target resources

If you are replacing or updating most of the rows then truncate and recreate will probably be faster, if you only need to affect relatively few then partial updates are likely to be faster but this is not always the case.

If truncate+replace does turn out to be faster (or only slightly slower) you may find dropping all indexes (apart from the clustered index) and recreating them after the copy will improve matters further.

  assuming the majority of rows in each table are not new this week/month

In this instance if you know exactly what timestamp you need data from (this can usually be derived from the target's current data, or you could store the relevant value for future reference after each process) and you can efficiently ask the sources for only the updated data, then this will definitely be faster. Even if for some reason you only know roughly and have to request data a bit further back to make sure you get everything, you will not only be writing less at the destination but will be transferring less from each target (just the recent rows) and from the destination if needing to merge (again, just the recent rows) than you would pulling all the data from the sources.

  I can't currently test which is faster as the initial transfer is still going

You can do some useful analysis at this point though: what seems to be the current bottleneck? It could be:

waiting for data to be read from the source(s) - i.e. their IO subsystem or DB design is limiting the processes speed, in thsi case you'll see high IO at that end from your process and less activity elsewhere
waiting for data to be transferred from the source(s) - i.e. bandwidth is a limiting factor, is that link saturated while everything else is more idle?
processing by SSIS if your ETL is more complicated than "read from here, write to there" - i.e. are you seeing a lot of CPU and/or IO activity from SSIS?
waiting for data to be transferred to the target - as above but in reverse
waiting for data to be written at the target - as above but in reverse
... or of course some combination of the above

Keep watching as the tasks proceed: does it look like the same key bottleneck(s) most of the way through the process?


Filtered index not chosen, and rejected with hint I'm trying to understand why a non-clustered index is not available for a given query when filtered.  The relevant part of my (large) query is this:

) results
JOIN BE_Insurance ins
ON results.PayorId = ins.Id

Up in the select, I'm only grabbing ins.name.  The index I initially created was this:

CREATE INDEX IX_BE_Insurance_PayorId_PayorName
ON      BE_Insurance (Id) INCLUDE (Name)
WHERE ParentId IS NULL

My query is such that only payorIds with a NULL parentId will be chosen, but I understood the optimizer's disinclination to choose it.  But when I added a hint to try to force the index, the whole thing error'd out.

  Query processor could not produce a query plan because of the hints defined in this query. Resubmit the query without specifying any hints and without using SET FORCEPLAN.

I assumed it would follow my hint, and maybe error out at execution time if I wound up with some bad data and the index was missing some needed values.  Removing the filter from the index caused it to be chosen successfully by the query (even without the hint).

Are indexes with a WHERE clause ineligible for queries like this?  Are they eligible only if the optimizer can be guaranteed that the filter is valid and won't produce any missing values?

As requested, here's the whole query, which is still a work in progress.

SELECT results.*
FROM (
        SELECT auths.*,
               worked.WorkedHours,
               worked.WorkedUnits,
               worked.WorkedAmount,
               worked.ActiveClients
        FROM (
                SELECT PayorId,
                       SUM(AuthHours) AuthHours,
                       SUM(AuthUnits) AuthUnits,
                       SUM(AuthAmount) AuthAmount
                FROM (
                        --DYNAMIC TEMPLATE ----------------------------------------------------------------------------------
                        SELECT PayorId,
                               PayorName,
                               AuthHours AuthHours,
                               AuthUnits AuthUnits,
                               AuthAmount AuthAmount
                        FROM PayorAuthorizations_Level1Data_Authorizations auths WITH(NOEXPAND)
                        WHERE OrganizationId = @organizationId AND StartDate <= @endDate AND @startDate <= EndDate
                        --/DYNAMIC TEMPLATE----------------------------------------------------------------------------------

                        --INTERSECT / EXCEPT dynamically generated queries
                ) q
                GROUP BY PayorId
        ) auths
        JOIN (
                SELECT PayorId,
                       SUM(ISNULL(TotalWorkedHours, 0)) WorkedHours,
                       SUM(ISNULL(TotalWorkedUnits, 0)) WorkedUnits,
                       SUM(ISNULL(TotalWorkedAmount, 0)) WorkedAmount,
                       COUNT(DISTINCT clientId) ActiveClients
                FROM (
                        --DYNAMIC TEMPLATE ----------------------------------------------------------------------------------
                        SELECT PayorId,
                               TotalWorkedHours,
                               TotalWorkedUnits,
                               TotalWorkedAmount,
                               clientId clientId
                        FROM PayorAuthorizations_Level1Data_CurrentlyWorked worked WITH(NOEXPAND)
                        WHERE OrganizationId = @organizationId AND StartDate <= @endDate AND @startDate <= EndDate
                        --/DYNAMIC TEMPLATE----------------------------------------------------------------------------------

                        --INTERSECT / EXCEPT other dynamically generated queries
                ) q
                GROUP BY PayorId
        ) worked
        ON auths.payorId = worked.payorId
) results
JOIN BE_Insurance ins WITH (INDEX(IX_BE_Insurance_PayorId_PayorName))
ON results.PayorId = ins.Id
OPTION(FORCE ORDER, MERGE JOIN)

I assumed it would follow my hint, and maybe error out at execution time if I wound up with some bad data and the index was missing some needed values.

The query optimizer will only use a filtered index in a query plan if it can guarantee (within its reasoning framework) that all possible matches can be served from the index. This is by design, to avoid the sort of runtime error you describe.

  Failure to results in a NESTED LOOPS JOIN from my non-clustered index against a clustered index Key Lookup, presumably to grab the parentId. INCLUDING parent ID eliminates this, and leaves me with a nice non-clustered index scan.

This is a known current limitation. Adding the filtered column(s) to the key or include list is the standard workaround, and a current best practice for all sorts of semi-related reasons.

  The FORCE ORDER, MERGE JOIN is definitely needed though.

Be extremely careful using hints (directives) like this unless you fully understand all the consequences. FORCE ORDER in particular is an extremely powerful and wide-ranging hint, with a number of non-obvious side-effects including the placement of aggregate operators, and the order of evaluation of subqueries and common table expressions.

For the most part, you should try to write queries that provide the query optimizer with enough good-quality information to make the right decisions without hints. The hinted plan may be 'optimal' today, but it may not remain so as the data volume and/or distribution changes over time.


Need help in picking solution and understanding when to add columns vs EAV I've been working on the following problem for a long time and I can't decide between two solutions. While both would work to solve the problem, neither "feels" exactly right to me.

Any advice on this would be greatly appreciated. Also note that I'm not particularly great with DB design so any feedback is appreciated.

I have the following table:

ProductYearDistrictCrop (+-2 million records)

oid | productId | yearId | districId     | cropId | payDate     |
1   | 1         | 1      | 1             |1       | 2018-01-01  |
2   | 1         | 1      | 1             |1       | 2018-02-01  |
3   | 2         | 1      | 1             |1       | 2018-03-01  |
4   | 2         | 1      | 1             |1       | 2018-04-01  |

Some background info on the table:

A record is created for each possible combination of Product - Year - District - Crop and there is a unique constraint on those Foreign Keys (the combination must be unique). I opted to use Oid as the PK instead of a Composite Key because Oid can then be used by other tables as a FK and I assumed a lookup on the Oid would be quicker than a composite key.

For each of these records, there are additional options that can be picked from. These options come from the below table

Options (16 records)

oid | description   | systemDefaultValue
1   | Option 1      | 0.5
2   | Option 2      | 2.5
3   | Option 3      | 1.5
4   | Option 4      | 1.5

For each of these choices I need to store a custom value or just reuse the systemDefaultValue per record in ProductYearDistrictCrop

The two possible solutions I come up with are

Solution A : Simple add each possible option as column to the table

oid | productId | yearId | districId     | cropId | payDate     | Option1   | Option2   |
1   | 1         | 1      | 1             |1       | 2018-01-01  | 0.5       | 2.5       |
2   | 1         | 1      | 1             |1       | 2018-02-01  | 0.5       | 2.5       |
3   | 2         | 1      | 1             |1       | 2018-03-01  | 0.5       | 2.5       |
4   | 2         | 1      | 1             |1       | 2018-04-01  | 0.5       | 2.5       |

Which means we'll end up with 16 extra columns in ProductYearDistrictCrop and of course, each time a new option is added to the system, I need to update this table as well. While this doesn't happen often, it can happen.

The other consideration is that there are other tables that will also follow a similar design so that means I would have to update several tables each time a new option is added.

Solution B : Use what I think is an EAV approach for the options by creating a linking table

ProductYearDistrictCropOptionChoices

ProductYearDistrictCrop  | optionId  | customValue |
1                        | 1         | 0.5         |
2                        | 2         | 1.5         |
3                        | 3         | 100         |
4                        | 4         | 25          |

In this scenario I end up with a +- 32 million records. The obvious upside is that no db changes are required each time a new option is added. Also I'm working with worst case scenario so it's not that each record from ProductYearDistrictCrop WILL have 16 records linked to it.

My Feedback

My gut feeling to pick Solution A and add the columns for each option. I can then do a single lookup when I need the data and get everything I need from that row or sets of rows.

The other reason I prefer Solution A is that to my understanding attributes should be columns in a database. It's just that another table is already storing those attributes as options to be used by the system when building UIs and providing hte user with default options and values.

I've been wrestling with this for a long time and would just like to get some external advice and insights. Also it would help to get more opinions as I would like to justify my choice with a stronger reasoning than "I feel this one is good and that one is icky"


Make rows immutable allow Insert mysql5 I want to make the rows in a Mysql table immutable, once they are inserted I don't want it to be possible to change them. How can I do this?

(ideally returning a suitable error message if any change is attempted).

I dont have grant privileges, so i would like to understand how to write BEFORE UPDATE trigger that raises an error (using signals or issuing a statement that always fails).

@NathanJolly's comment about using privileges is really the correct answer, since if a user actually has the privileges to delete or update the table, they could likely find a way to work around the suggestions below.

The tactics discussed below should be considered a "soft" immutability implementation -- it's only really good for keeping honest people honest by preventing them from doing things they can but shouldn't.  It will not prevent deliberate malicious action by a user with too many permissions.

You can do this in a BEFORE UPDATE and BEFORE DELETE trigger.  It won't work properly in an AFTER trigger, since the work has already been done.

In MySQL 5.5 and above:

Prevent deletion:

mysql> CREATE TRIGGER t1_bd BEFORE DELETE ON t1 FOR EACH ROW SIGNAL
       SQLSTATE '45000' SET MESSAGE_TEXT = 'table `t1` does not support DELETE';

Attempts to delete will throw a fatal exception on the first row, stopping the query, and the row(s) in question will not be deleted.

mysql> DELETE FROM t1 WHERE id = 1;
ERROR 1644 (45000): table `t1` does not support DELETE

Prevent updates with a similar trigger:

mysql> CREATE TRIGGER t1_bu BEFORE UPDATE ON t1 FOR EACH ROW SIGNAL
       SQLSTATE '45000' SET MESSAGE_TEXT = 'table `t1` does not support UPDATE';

The MESSAGE_TEXT argument can take a string of up to 128 characters.

Prior to MySQL 5.5 you can throw an error by calling a procedure that doesn't exist.  The error message will be in the form:

ERROR 1305 (42000): PROCEDURE {schema}.{nonexistent-procedure-name} does not exist.

Given this bit of common structure, you can create a nonsense procedure name using backtick escapes that will form the first part of a sentence that makes sense when the phrase "does not exist" is appended to the error message by the server.

mysql> CREATE TRIGGER t1_bd BEFORE DELETE ON table_name FOR EACH ROW
       CALL `t1: DELETE capability for this table`;

This suggestion is bound to leave people scratching their heads until you see the error that it returns:

mysql> delete from t1 where id = 2;
ERROR 1305 (42000): PROCEDURE test.t1: DELETE capability for this table does not exist

lol... but it works.  Here, your "error message" content is limited to the maximum length of a stored procedure name, which is 64 characters.  The backticks make the spaces acceptable in the nonexistent procedure name.

Of course, if you're in 5.1 or previous and someone has privileges to actually create such a procedure, they can bypass this restriction by creating a procedure that does nothing, which will result in the trigger doing nothing and the delete/update succeeding.

In either case, if the user has permission to drop the triggers, the mechanism is easily bypassed in this way also... if a user can create tables, they could create a new table, INSERT ... SELECT from the old table to the new table, modify data as desired, rename the tables... so we're back to permissions as the correct solution again.

Caveat lector.


Comparing date and timestamp variables in a where clause in a procedure I have a variable in an Oracle stored procedure of DATE type (from user interface), and another of TIMESTAMP type (database is more precise than what the user would enter in the code that is executing the stored procedure).

I would like to do something like this: (pseudo code)

select * from MYDATABASE where inputDate = extract(date from myDatabaseTimeStamp);

but I get a compile error when I try this.  Any idea what is a good actual code solution for this?

Assuming the DATE from user interface can contain times besides 00:00:00 (midnight) and that you only want to know if the DATE from the user interface and the TIMESTAMP fall on the same day (not same hour or minute or ?), try this:

...

myDatabaseDate    DATE;
myDatabaseNextDay DATE;

BEGIN

  -- Change TIMESTAMP to a DATE type with CAST, then TRUNCate time to 00:00:00.
  myDatabaseDate    := TRUNC( CAST(myDatabaseTimeStamp as DATE) );
  myDatabaseNextDay := myDatabaseDate + 1;

  SELECT something into some_var
  FROM MYDATABASE
  WHERE inputDate < myDatabaseNextDay
  AND inputDate >= myDatabaseDate;

  ...

Note 1: Because "inputDate" is a database column and could be indexed, we don't want to do TRUNC(inputDate) because then the index won't be used.

Note 2: Doing the CAST before the SQL statement prevents this PL/SQL compilation warning: PLW-07204: conversion away from column type may result in sub-optimal query plan ... Edit: After testing this on 10.2 XE, even the "myDatabaseDate + 1" causes the warning so, although it seems like doing too much to please the compiler, I added "myDatabaseNextDay := myDatabaseDate + 1;"


Connection failed: No such file or directory Mysql - WSL Sorry if i posted incorrect section.
I have Windows with this WSL:

PRETTY_NAME="Debian GNU/Linux 10 (buster)"
NAME="Debian GNU/Linux"
VERSION_ID="10"
VERSION="10 (buster)"
VERSION_CODENAME=buster
ID=debian

On my WSL i installed php7.3-fpm and Nginx, i've tested and both were working. I can't installed mysql on WSL i've got much errors then i installed Mysql on my Window and created a simple database.

The problem is when i try to connect my database with this code:

<?php
$servername = "localhost";
$username = "root";
$password = "root";

// Create connection
$conn = new mysqli($servername, $username, $password);

// Check connection
if ($conn->connect_error) {
    die("Connection failed ttt: " . $conn->connect_error);
}
echo "Connected successfully";
?>

And I got this message:

  Connection failed: No such file or directory

I think it's because my Mysql is on Windows and php interpreter is on WSL and can't comunicate.
Someone can help me please.

in PHP  mysqli needs 4 Paarameters

https://www.php.net/manual/de/function.mysqli-connect.php

So it should be

<?php
$servername = "localhost";
$username = "root";
$password = "root";
$database = "mydatabase";

// Create connection
$conn = new mysqli($servername, $username, $password,$database);

// Check connection
if ($conn->connect_error) {
    die("Connection failed ttt: " . $conn->connect_error);
}

echo "Connected successfully";
?>


Not able to change the AUTO_DEL_REC_OBJ value to ON I changed the value of AUTO_DEL_REC_OBJ to on as below in UAT and prod environment; in UAT it's done but in prod its current value is still showing OFF. What did I do wrong?
In UAT:
db2 update db cfg for CMNDB using AUTO_DEL_REC_OBJ ON

 Description                                   Parameter   Current Value              Delayed Value
 -------------------------------------------------------------------------------------------------------
Auto deletion of recovery objects    (AUTO_DEL_REC_OBJ) = ON                         ON

In Prod:
 Description                                   Parameter   Current Value              Delayed Value
 -------------------------------------------------------------------------------------------------------
Auto deletion of recovery objects    (AUTO_DEL_REC_OBJ) = OFF                        ON

Why has it changed in the UAT server but not in Prod?

You must connect to the database first to change some parameter online, if it's possible for this parameter:
db2 connect to CMNDB
db2 update db cfg using AUTO_DEL_REC_OBJ ON
db2 get db cfg show detail | grep AUTO_DEL_REC_OBJ


Why do nonclustered Indexes use clustered indexes instead of the table data pages directly? From a Microsoft Course (presented by a third party) I learned that when a table has a clustered index even non-clustered indexes hit the clustered index.

When there is not a clustered index (Heap) as I understand it Nonclustered indexes reference directly into the pages of the table.

Assuming correct understanding, why do the non-clustered indexes reference the clustered index instead of the underlying data pages of the table directly? It seems like this would be less overhead referencing the table data pages directly.

The clustered index IS the table. In the classic phone book analogy where everything is ordered by Lastname, Firstname there's not some other book that the phone book refers to.  The Phone book IS the book, and it's in that order.

When you have a clustered index, it contains all the data for all the columns in the table, ordered by the key you pick.

In the absence of a CI, the non-clustered indexes need to refer to something so they refer to the RID (row identifier) which is a surrogate key the engine assigns.


How To Hide Schema from user I have DB with 2 schemas: A and B.  B schema's procedures use A schema's procedures.

I need to create user, which will not be able to see A schema. This user can use only B schema, but i want to let the user execute B's procedures that use A's procedures...

The basic concept is to use GRANT/DENY Schema Permissions. You can efficiently manage permissions by creating a role and then adding members to it.

Below is an example that will explain you in detail

use master
go
--Create Logins
CREATE LOGIN UserA WITH Password='UserA123';
go
CREATE LOGIN UserB WITH Password='UserB123';

use AdventureWorks2008R2
go
--Create Database Users
CREATE USER UserA;
go
CREATE USER UserB;
go
--Create the Test Schemas
CREATE SCHEMA SchemaA AUTHORIZATION UserA
go
CREATE SCHEMA SchemaB AUTHORIZATION UserB
go

-- create test tables
create table schemaA.TableA (fname char(5))
go
insert into schemaA.TableA (fname) values ('Kin-A')
go

create table SchemaB.TableB (fname char(5))
go
insert into SchemaB.TableB (fname) values ('Kin-B')
go

Now test :

--Test for UserA in SchemaA
EXEC('select * from schemaA.TableA') AS USER = 'UserA'
go
--Kin-A

-- Test for UserB in SchemaB == this should fail
EXEC('select * from SchemaB.TableB') AS USER = 'UserA'
go
--Msg 229, Level 14, State 5, Line 1
--The SELECT permission was denied on the object 'TableB', database 'AdventureWorks2008R2', schema 'SchemaB'.

Now create Stored Procedures :

CREATE PROCEDURE SchemaB.proc_SelectUserB
AS
    select * from schemaA.TableA;
go
create procedure schemaA.proc_SchemaA
as
    select * from schemaA.TableA

Now Grant execute permissions to UserA on schemaB's SP

GRANT EXECUTE ON OBJECT::[SchemaB].[proc_SelectUserB] TO [UserA]
go

Test it .. to see if UserA is able to run SP from schemaB. This will PASS

EXECUTE AS LOGIN='UserA';
    Exec SchemaB.proc_SelectUserB;
    revert;
go
--- Kin-A

But UserA wont be able to see data from SchemaB

EXECUTE AS LOGIN='UserA';
    select * from SchemaB.TableB
revert;
go

--- Msg 229, Level 14, State 5, Line 3
--- The SELECT permission was denied on the object 'TableB', database 'AdventureWorks2008R2', schema 'SchemaB'.

Alternatively you can use DATABASE ROLE and just add users to it for better manageability of permissions:

EXEC sp_addrole 'SchemaBUsesSchemaAProc'
go
EXEC sp_addrolemember 'SchemaBUsesSchemaAProc','UserA';
go

Below statement will make sure that UserA is able to see schemaA and NOT schemaB. The good thing is that you can just add users to SchemaBUsesSchemaAProc role and they will inherit all the permissions granted to that role.

GRANT SELECT ON SCHEMA::SchemaA TO SchemaBUsesSchemaAProc;
go

If you only want to allow UserA to execute SP's which are owned by SchemaB then below statement will do the job:

GRANT EXECUTE ON OBJECT::[SchemaB].[proc_SelectUserB] TO [SchemaBUsesSchemaAProc]
go

This way, UserA is not able to see SchemaB's tables, but still can execute procs from SchemaB.

Below will explain the permission hierarchy :


Opening a stored procedure file multiple times in sql server management studio In Sql Server Management Studio, there is strange situation that made me lose a full day work. It is the problem of opening database objects multiple times. When I try to edit a table by right clicking from the object explorer, it is opening the edit screen for the first time, at second time it is re-opening the previously opened but not in a new edit screen. Despite that, if I try to select from that table by right clicking from the object explorer, it is opening the new query with the select statement multiple times while I retry this process. It is also doing exactly the same thing for the stored procedures and functions. I lost my changes in a stored procedure because of opening multiple times.In Visual Studio it is not possible to re-open a file in new editor multiple times by default. Are there any option to reconfigure sql server management studio to not open an object which is already opened in editor?

Note: I know this is my problem to remember which objects I have already opened, but I am asking for help, if there is an option that I am not aware.

Important EDIT:
I need to clarify that I can understand the SQL programming and I am not a newbie. I just don't used to SSMS and I don't know the all features of that tool. In my question's Note Section, I marked that it is my responsibility to keep records of scripts. I am only asking for if there is an option to prevent opening same script multiple times. If your answer is not more than suggesting to using third-party tool etc. please keep it for yourself.

No, there is no such option.

SSMS doesn't open the database object and lock it down for you like Visual Studio w/ Data Tools add on does. Instead, it generates a sql script depending on what you want to do. It is more of a "Oh, you want to modify 'dbo.sp_awesomeness'? Not a problem! Here is the sql script containing alter procedure code you need at the time you asked of it." 2 minutes later, you ask again, and it pops open a new window going thru the same as above. It is always giving you the information at the time of the request to SSMS.

Visual Studio does the "Oh hey, you want the dbo.sp_awesomeness! Sweet. I see you checked it out of git hub, that's all good. Here is the copy." When you ask for it two minutes later, it just brings up the original screen. But then again, you are not creating sql scripts from SSDT. You are building a database package to deploy.


Set default database for connection Super n00b question, I know, but I can't seem to find it anywhere that's easy to find, so it would be good to document this here.

Say I connect to a Teradata system as a particular user, NICK, but I need to access tables owned by a different user, BOB.

Instead of having to qualify each table name with BOB. I'd like to just set my default database for the connection to BOB and then query the tables using their unqualified name.

In other words, instead of this:

-- Cartesian Join of Doom (CJD™)
SELECT COUNT(*)
FROM BOB.TABLE1, BOB.TABLE2, BOB.TABLE3;

I want to be able to do this:

-- CJD™ for the lazy
SELECT COUNT(*)
FROM TABLE1, TABLE2, TABLE3;

Is there a way to do this for the life of a connection?

You just need to issue:

database dbname

Similar to an alter session set current_schema in oracle.


Remove duplicate rows from select query using group by I have a situation to grab rows with a minimum value of a certain column. Below is the image of my made up table to explain what I am trying to do.

I need to pull one record per instance_oid with the minimum shelf number. I wrote the query like this:

select instance_oid, site, bldg, room, min(shelf) group by instance_oid, site, bldg, room, shelf

This gives same record in multilpe rows (as many rows as I have for each instance_oid). How do I make sure I get only one record, i.e, the one with the min(shelf) as I highlighted in the attached image?

take off shelf at the end of your group by

select instance_oid, site, bldg, room, min(shelf) group by instance_oid, site,  bldg, room

Note:
As mentioned in a comment the sites are different (and not highlighted in yellow), but I'm assuming you want newly grouped row output when that happens...

if not I would not include the site data:

select instance_oid, bldg, room, min(shelf) group by instance_oid, bldg, room


Could running ANALYZE on all databases on large InnoDB tables show some performance increase? So we have an old database that was fragmented until we ran OPTIMIZE on all tables.

The database/tables are about 100GB in size.  Tables are InnoDB with heavy read/write activity.

There are so many different queries we decided to disable query_cache due to over 1 million+ queries being pruned daily, even with 512MB cache size (with 4mb min).

Would ANALYZE actually improve performance?

There are a few situations where running ANALYZE TABLE is beneficial

When you have innodb_stats_auto_update=0 set
When your statistics are already "messed up" and need a fixin'

If your table statistics frequently get into a "messed up" state (which would cause bad or strange query plans), as can be the case when a table has nightly purge or bulk insert jobs, you should consider setting innodb_stats_auto_update=0 and running a periodic manual ANALYZE TABLE.  Otherwise, let sleeping dogs lay and don't run it.

One caveat about ANALYZE TABLE is that it can cause problems if you have long running queries against the table because it flushes the table.  This means that, if you run ANALYZE TABLE while a query is running, no other tables will be able to access the table until the first query finishes.  I wrote ab-analyze-innodb to help address this problem by searching the process list to identify whether or not the table is actively being accessed.


migrating DBs from SQL server 2005 to SQL server 2012 We are planning on migrating from SQL server 2005 to SQL server 2012,the DB's are huge and cannot be backed up so we are doing a detach and attach method. I read online SQLServer 2005 to 2012 cannot be updated straight away. I think we need to upgrade to 2008r2 and then upgraded to 2012 not sure if it is the best practice . I got the users & permission scripted out . What are the best practices to follow to have a smooth upgrade without any issues.

Depending on the edition since the database size is big, you can look into

Logshipping - set up with full backup and subsequent log backups. During the cutover, just take the final tail log backup and restore the 2012 server databases with RECOVERY.
Mirroring - Set up as async and during cutover, change the mode to SYNC and failover.

Once the failover is done, remove logshipping or mirroring.

I agree with alroc that the dbatools.io is extremely useful, but in your situation you are better off going with above and separately use the cmdlets for migrating jobs, logins.

For more detailed steps, refer to my answer here.


what are the checklist when looking at MySQL database for first time? What should I put on my checklist when I look at MySQL database for the first time? I would appreciate your help. Thanks.

This question is very general and I'd suggest clarifying it with more context, but I'll give it a shot.

I would look at:

security.  What are the users that have privileges on that database?  What are the privileges (i.e. read-only on specific tables, or do they have grant option permissions)?
entities.  What are the major entities and their relationships?
performance.  What queries are resource hogs?  Are appropriate indexes in place?
denormalization.  Is the schema purposely denormalized?  If so, why and is it appropriately denormalized?
foreign keys.  Are they enforced (do the tables use InnoDB)?
size.  What are the biggest tables?  Which tables are expected to grow the fastest?


Make column default a query Is there a way I can set up a column in PostgreSQL 9.2 such that the default value is a query? I am having to track down query statements from different sources that don't respect the "default" behavior that would like to be seen in the various projects that are writing to the DB.

INSERT INTO Person(name, token, priv, sc)
VALUES ('Frank Josephson', '**fake token**', 'user'
      , (SELECT COUNT(*) FROM Person WHERE name='Frank Josephson'));

Otherwise, it's just me auditing a bunch of excel sheets and applications to make sure this new directive goes through. Any way to set this up as a trigger without running into race conditions?

Most likely a default value or trigger are not suitable solutions. The values inserted automatically are snapshots and quickly invalidated by subsequent writes to the table.
You should use a VIEW or a "generated column".
Example for "generated column"
All you need is a function that takes the table type as parameter:
CREATE OR REPLACE FUNCTION sc (person)
  RETURNS bigint AS
$func$
SELECT count(*) FROM person WHERE name = $1.name
$func$ LANGUAGE sql STABLE;

Now, you get the current count with
SELECT *, p.sc
FROM   person p
WHERE  name = 'Frank Josephson';

Details in the related answer on SO:

Store common query as column?


How does OPTION (FAST 1) actually interact with a client? This is question 1 of 2 related to OPTION (FAST 1);

We've just upgraded our ERP database from SQL 2000 EE to 2008 R2 EE and we've been noticing increased blocking in the database. I've narrowed it down to what I believe to be the offending statement in the vendor's code which is:

SELECT MAX(column)
FROM [table]
WHERE <condition>
OPTION (FAST 1);

The spid leaves an open transaction and takes a lock on the table, blocking all other clients. However, the calling client no longer seems to be interacting with the server to tell the server that it's received the data to end the session.

Reading the documentation on Query Hints, I saw this statement

  FAST number_rows

  Specifies that the query is optimized for fast
  retrieval of the first number_rows. This is a nonnegative integer.
  After the first number_rows are returned, the query continues
  execution and produces its full result set.

This makes me wonder if the client has somehow broken communication, would the server keep the transaction open, processing the full result set after the first n rows are returned and leave the transaction open? The process is an internal process so I can't really watch an end-user execute the session to do it, and this is not something that happens every time the internal process occurs. However, it is only ever used by the internal process.

Having read Remus' answer on SO it seems like it is overkill for the simplicity of the query. Looking at the query, if they're receiving more that one result from an ungrouped MAX then something's very fishy.

So, as I prepare to work with the vendor, I was wondering if I could begin to accurately pin our blocking issues on the fact that this query hint is being used.

Please feel free to edit/request edits as I know this may actually be unclear.

If it were a query that returned more than 1 row I'd speculate that someone at the vendor (way back when, given the version of SQL Server) stumbled on the query optimiser producing a preferable plan when FAST was specified as a hint.

As it's returning just 1 row, the explanation probably has more in common with the infinite monkey theorem than reasoned judgement. A junior saw a hint for FAST and decided that was preferable to his query not being fast.

  This makes me wonder if the client has somehow broken communication,
  would the server keep the transaction open, processing the full result
  set after the first n rows are returned and leave the transaction
  open?

The query returns 1 row so there is no further result set to process. I'm inclined to suspect there is other code further up the stack that's causing the problem.


Reached maximum possible value for auto_increment function set on column I'm using a MySQL database. I have set an auto_increment function on "ID" column (which is also set as Primary key).

What will happen when it reaches its maximum possible value, according to the data type set for that column?

Can someone help me out what to do in such cases? Will it be handled automatically? Or will I have to handle it?

While inserting single record (by both INSERT .. VALUES and INSERT .. SELECT) you will obtain

  Duplicate entry 'max_value_for_datatype' for key 'PRIMARY'

While inserting multiple records you will obtain (even when only one of them causes overflow)

  Failed to read auto-increment value from storage engine

fiddle

  Will it be handled automatically?

No. You must handle it - and the best way is to solve this issue before it occures.

PS. If you are interested in to know what messages will be produced while inserting record(s) by LOAD DATA/XML you may test it by yourelf.


Which views can be queried while instance is up but database not mounted in Oracle 11G? If I understood it correctly, when the instance is started, some views are loaded into memory and we can query against it like v$instance. And it doesn't need db to be mounted.

If so, are there any other views which can be queried, apart from v$instance?

Also, when I query v$database, it says database is not mounted, but a different error when I try to give a random name. Is it just different error handling for the view name v$database (as it seems to be considered something important or known database) or any other reason for this?

SQL> select * from v$database;
select * from v$database
              *
ERROR at line 1:
ORA-01507: database not mounted

SQL> select * from v$data;
select * from v$data
              *
ERROR at line 1:
ORA-01219: database not open: queries allowed on fixed tables/views only

SQL> select * from testing;
select * from testing
              *
ERROR at line 1:
ORA-01219: database not open: queries allowed on fixed tables/views only

Update:

It seems the following views also work: v$session, v$sql.

The reason for asking is I just want to understand the scope and purpose of each view or database table practically.

Generally speaking, most of the dynamic performance views, known as V$ views, which are populated in the memory, SGA to be specific are accessible after the instance startup process. If the these V$ views contains information which are stored in physical files such as control files or data files then your database has to be mounted and/or opened.

For example, when your database instance is started but database is unmounted, you can describe the v$database view but can't access it's contents until the database is mounted. This is due to the fact that the view has to get information from control files which is inaccessible until the database is mounted.

SQL> startup nomount

Instance is started.

SQL> desc v$database

Can view the structure becuase it is pupulated in SGA.

SQL> select * from v$database;
select * from v$database
              *
ERROR at line 1:
ORA-01507: database not mounted

SQL> alter database mount;

Database altered.

SQL> select * from v$database;

Now, you may access it's contents.

v$instance is accessible after the instance is started. As it's name suggests, it contains information related to memory and processes.


MySQL - How to replace a given value from multiple columns when using SELECT? I need to SELECT a set of rows of a given table whose structure looks like - 1º line is the name of columns

    A01    A02    A03    A04    A05  ... A200

  99999  99999     10    753  99999     99999

As you can see, it has about 200 columns. It uses 99999 to model a NULL or undefined value. So, in my SELECT, i need to replace those values with NULL or even a empty String. Is it possible by using a single SELECT or should i use something else ?

ATT: you could suggest UPDATE those values with NULL - and then SELECT -, but it is a legacy system which we cannot modify them

SELECT IF(A01 = 99999, NULL, A01) AS A01,
       IF(A02 = 99999, NULL, A02) AS A02,
       ...
    FROM ...

(For empty string, use '' instead of NULL.)


Is there a performance hit for querying two different databases? I have two tables, A and B, which are located in different databases on the same MySQL server.  I would like to create a derived table C from tables A and B, as an inner join between the two tables.

Something like this (pseudo-code):

create table C as select A.gene, B.pubmed_id, B.pubmed_text from A inner join B ...

Will there be a performance hit in doing so? Would it be more prudent to load tables A and B into the same database? These are both very big tables with millions of records.

There is essentially no difference between table and db.table.  Once the tables (for SELECT or for INSERT) is opened, it is just a table, regardless of what database it is in.


Include logical key on each table vs. join I'm designing a database that is used to track an inventory item through its lifecycle. I'm currently relating my tables using the previous lifecycle event's ID (for example, a shipment contains the inventory item's ID, the receiving department table stores a shipment ID, the sales table stores the receiving receipt ID, and so on). I've designed the tables in this way to enforce the fact that each of the lifecycle steps must happen in a particular sequence.

If I wanted to check information about a particular inventory item, I'd need to go through and join all the tables that represent that item's lifecycle. Each of my inventory items has a unique serial number, so I'm thinking about including that SN on specific lifecycle event tables in order to avoid having to perform all the joins.

I was wondering, is there a rule or guideline as to when you should use key duplication vs performing a bunch of joins? If I were to go with the joining method, would this be an appropriate place to use a bunch of different views?

"to enforce the fact that each of the lifecycle steps must happen in a particular sequence."

If you want to enforce referential integrity of an item's life-cycle, then I would recommend using essential integrity enforcing tools built into the product, namely constraints.

Having independent keys is good for quickly getting a record specific to a table, but it seems you're not going to get a lot of the benefits of constraints by trying to 'roll your own'.


Migrating Data from SQL Server 2005 to 2016 I am new to administering SQL Server however I am comfortable with the SQL language and making SSIS packages.

I want to migrate data from SQL Server 2005 to 2016.

My question is do I need to worry about the system databases and other objects such as indexes, stored procedures, view, security and permissions. logins or can I just migrate data.

Which would be the recommended procedure in this case.

Max gave a decent answer which I will upvote once I'm done typing this alternate view up.

I am not a fan of restoring system databases when doing an upgrade migration and I prefer doing migrations over in place upgrades as I discussed in this lengthy answer to another question.

Basically I like to start "fresh" when I do a migration. I find that playing with system database migrations and upgrades through restore to sometimes cause frustrations with the restores and it can carry over potential sins.

You also asked about indexes, stored procedures, views. Those items at the database level should all live inside of a user database. So when you restore database X to the new server, all of the database objects (Tables, Users, Views, Procs, Functions, etc) will be there as well.

What exist in the system databases are jobs, logins, alerts, linked servers, encryption keys, etc. Instance level items.

I like to review those and migrate over what I need using various scripts - lately that is the DBATools.Io powershell scripts. I like using their script to copy sql logins especially, because it handles the SQL authenticated users keeping their passwords and security identifiers the same so the database users from those logins will work. They also have an entire SQL Server migration command which runs their sub commands to copy over the items I typically would copy over.

I do not believe Max to be wrong with that answer  hence the upvote. I just have had more success and more luck and feel more comfortable migrating to new instead of trying to restore over system databases between versions. I would say that I've honestly can't remember the last time I did a version upgrade migration and didn't do it this way instead of restoring the system databases.


SSDT on VS2013 for SQL Server 2012 I have Visual Studio 2013, and SQL Server 2012. From what I'm reading, it seems that the SSDT add-in for VS2013 only works for SQL Server 2014. Is that correct?

I've installed it, and I built a SSIS package that runs fine from Visual Studio. But when I attempt to import it to SQL Server, I get a generic

  0xC0010014 "One or more error occurred...This occurs when CPackage::LoadFromXML fails...

My guess is the DTSX format isn't readable by SQL Server 2012.

Is there something I can do to make VS2013 save in SQL Server 2012 format, or another way to import my package to SQL Server? I'd rather not have to install VS2012 on this machine since I've already got 2013.

That is correct. Forward compatibility is guaranteed. Backwards compatibility does not exist*. Having 4 different BIDS/SSDT instances on a machine is a pain I've known all too well but there's not much you can do.

There are ways of constructing SSIS packages such that your solution can target whatever version of SQL Server will be hosting them but that's installing, purchasing and learning a separate product (Mist).


Is this table in 3NF? I have a table person with attributes:

id
name
city
state
country

id is the primary key. Is this table in 3NF? If 1000 people live in same city it seems redundant to store the same data for state, city, and country.

No it is not.

Consider this data

id | name | city        | state      | country
----------------------------------------------
 1 | john | Los Angeles | California | USA
 2 | mary | San Antonio | Texas      | USA
 3 | joe  | Los Angeles | California | USA

It is clear that (city,state,country) is a candidate key for another table.  Then, you would quickly realise that the cities belong to the same (state,country) combinations.  Eventually, you end up with

Person
------
id
name
city_id

City
--------
city_id
name
state_id

State
--------
state_id
name
country_id

Country
----------
country_id
name


Need to convert date to word using trigger I have created a datetoword function in Mysql. Checked the function working fine. Now using this function need to convert the date from field1 (date-datetype) to field2 (Varchar-datatype) within the table using trigger on before insert.

Tried with some trigger code

Set New.dobw = CALL date_to_words(New.dob);

Help needed in this regard.

Show function status;  (output is shown in screenshot)

SELECT date_to_words(NOW())

Based on your comment, the trigger has multiple lines.

You need a BEGIN...END block as follows:

USE mcet
DELIMITER $$
DROP TRIGGER IF EXISTS tg_student_details_insert;
CREATE TRIGGER tg_student_details_insert BEFORE INSERT
ON student_details FOR EACH ROW
BEGIN
    SET NEW.created_date = NOW();
    SET NEW.dobw = date_to_words(NEW.dob);
END;
DELIMITER ;

You answered this question before (How to autoincrement Prod_id based on Product_Name using trigger in mysql). In that post, you used a BEGIN...END block for four(4) lines of code. You needed one for this post as well.


Configuring PostgreSQL for read performance Our system writes a lots of data (kind of Big Data system). Write performance is good enough for our needs but read performance is really too slow.

The primary key (constraint) structure is similar for all our tables:

timestamp(Timestamp) ; index(smallint) ; key(integer).

A table can have millions of rows, even billions of rows, and a read request is usually for a specific period (timestamp / index) and tag. It's common to have a query that returns around 200k lines. Currently, we can read about 15k lines per second but we need to be 10 times faster. Is this possible and if so, how?

Note: PostgreSQL is packaged with our software, so the hardware is different from one client to another.

It is a VM used for testing. The VM's host is Windows Server 2008 R2 x64 with 24.0 GB of RAM.

Server Spec (Virtual Machine VMWare)

Server 2008 R2 x64
2.00 GB of memory
Intel Xeon W3520 @ 2.67GHz (2 cores)

postgresql.conf optimisations

shared_buffers = 512MB (default: 32MB)
effective_cache_size = 1024MB (default: 128MB)
checkpoint_segment = 32 (default: 3)
checkpoint_completion_target = 0.9 (default: 0.5)
default_statistics_target = 1000 (default: 100)
work_mem = 100MB (default: 1MB)
maintainance_work_mem = 256MB (default: 16MB)

Table Definition

CREATE TABLE "AnalogTransition"
(
  "KeyTag" integer NOT NULL,
  "Timestamp" timestamp with time zone NOT NULL,
  "TimestampQuality" smallint,
  "TimestampIndex" smallint NOT NULL,
  "Value" numeric,
  "Quality" boolean,
  "QualityFlags" smallint,
  "UpdateTimestamp" timestamp without time zone, -- (UTC)
  CONSTRAINT "PK_AnalogTransition" PRIMARY KEY ("Timestamp" , "TimestampIndex" , "KeyTag" ),
  CONSTRAINT "FK_AnalogTransition_Tag" FOREIGN KEY ("KeyTag")
      REFERENCES "Tag" ("Key") MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITH (
  OIDS=FALSE,
  autovacuum_enabled=true
);

Query

The query takes about 30 seconds to execute in pgAdmin3, but we would like to have the same result under 5 seconds if possible.

SELECT
    "AnalogTransition"."KeyTag",
    "AnalogTransition"."Timestamp" AT TIME ZONE 'UTC',
    "AnalogTransition"."TimestampQuality",
    "AnalogTransition"."TimestampIndex",
    "AnalogTransition"."Value",
    "AnalogTransition"."Quality",
    "AnalogTransition"."QualityFlags",
    "AnalogTransition"."UpdateTimestamp"
FROM "AnalogTransition"
WHERE "AnalogTransition"."Timestamp" >= '2013-05-16 00:00:00.000' AND "AnalogTransition"."Timestamp" <= '2013-05-17 00:00:00.00' AND ("AnalogTransition"."KeyTag" = 56 OR "AnalogTransition"."KeyTag" = 57 OR "AnalogTransition"."KeyTag" = 58 OR "AnalogTransition"."KeyTag" = 59 OR "AnalogTransition"."KeyTag" = 60)
ORDER BY "AnalogTransition"."Timestamp" DESC, "AnalogTransition"."TimestampIndex" DESC
LIMIT 500000;

Explain 1

"Limit  (cost=0.00..125668.31 rows=500000 width=33) (actual time=2.193..3241.319 rows=500000 loops=1)"
"  Buffers: shared hit=190147"
"  ->  Index Scan Backward using "PK_AnalogTransition" on "AnalogTransition"  (cost=0.00..389244.53 rows=1548698 width=33) (actual time=2.187..1893.283 rows=500000 loops=1)"
"        Index Cond: (("Timestamp" >= '2013-05-16 01:00:00-04'::timestamp with time zone) AND ("Timestamp" <= '2013-05-16 15:00:00-04'::timestamp with time zone))"
"        Filter: (("KeyTag" = 56) OR ("KeyTag" = 57) OR ("KeyTag" = 58) OR ("KeyTag" = 59) OR ("KeyTag" = 60))"
"        Buffers: shared hit=190147"
"Total runtime: 3863.028 ms"

Explain 2

In my latest test, it took 7 minutes to select my data! See below:

"Limit  (cost=0.00..313554.08 rows=250001 width=35) (actual time=0.040..410721.033 rows=250001 loops=1)"
"  ->  Index Scan using "PK_AnalogTransition" on "AnalogTransition"  (cost=0.00..971400.46 rows=774511 width=35) (actual time=0.037..410088.960 rows=250001 loops=1)"
"        Index Cond: (("Timestamp" >= '2013-05-22 20:00:00-04'::timestamp with time zone) AND ("Timestamp" <= '2013-05-24 20:00:00-04'::timestamp with time zone) AND ("KeyTag" = 16))"
"Total runtime: 411044.175 ms"

Data alignment and storage size
Actually, the overhead per index tuple is 8 byte for the tuple header plus 4 byte for the item identifier.
Related:

Use GIN to index bit strings
Calculating and saving space in PostgreSQL

We have three columns for the primary key:
PRIMARY KEY ("Timestamp" , "TimestampIndex" , "KeyTag")

"Timestamp"      timestamp (8 bytes)
"TimestampIndex" smallint  (2 bytes)
"KeyTag"         integer   (4 bytes)

Results in:

 4 bytes for item identifier in the page header (not counting towards multiple of 8 bytes)

 8 bytes for the index tuple header
 8 bytes "Timestamp"
 2 bytes "TimestampIndex"
 2 bytes padding for data alignment
 4 bytes "KeyTag"
 0 padding to the nearest multiple of 8 bytes
-----
28 bytes per index tuple; plus some bytes of overhead.

About measuring object size in this related answer:

Measure the size of a PostgreSQL table row

Order of columns in a multicolumn index
Read these two questions and answers to understand:

Is a composite index also good for queries on the first field?
Working of indexes in PostgreSQL

The way you have your index (primary key), you can retrieve rows without a sorting step, that's appealing, especially with LIMIT. But retrieving the rows seems extremely expensive.
Generally, in a multi-column index, "equality" columns should go first and "range" columns last:

Multicolumn index and performance

Therefore, try an additional index with reversed column order:
CREATE INDEX analogransition_mult_idx1
   ON "AnalogTransition" ("KeyTag", "TimestampIndex", "Timestamp");

It depends on data distribution. But with millions of row, even billion of rows this might be substantially faster.
Tuple size is 8 bytes bigger, due to data alignment & padding. If you are using this as plain index, you might try to drop the third column "Timestamp". May be a bit faster or not (since it might help with sorting).
You might want to keep both indexes. Depending on a number of factors, your original index may be preferable - in particular with a small LIMIT.
autovacuum and table statistics
Your table statistics need to be up to date. I am sure you have autovacuum running.
Since your table seems to be huge and statistics important for the right query plan, I would substantially increase the statistics target for relevant columns:
ALTER TABLE "AnalogTransition" ALTER "Timestamp" SET STATISTICS 1000;

... or even higher with billions of rows. Maximum is 10000, default is 100.
Do that for all columns involved in WHERE or ORDER BY clauses. Then run ANALYZE.
Table layout
While being at it, if you apply what you have learned about data alignment and padding, this optimized table layout should save some disk space and help performance a little (ignoring pk & fk):
CREATE TABLE "AnalogTransition"(
  "Timestamp" timestamp with time zone NOT NULL,
  "KeyTag" integer NOT NULL,
  "TimestampIndex" smallint NOT NULL,
  "TimestampQuality" smallint,
  "UpdateTimestamp" timestamp without time zone, -- (UTC)
  "QualityFlags" smallint,
  "Quality" boolean,
  "Value" numeric
);

CLUSTER / pg_repack / pg_squeeze
To optimize read performance for queries that use a certain index (be it your original one or my suggested alternative), you can rewrite the table in the physical order of the index. CLUSTER does that, but it's rather invasive and requires an exclusive lock for the duration of the operation.
pg_repack is a more sophisticated alternative that can do the same without exclusive lock on the table.
pg_squeeze is a later, similar tool (have not used it, yet).
This can help substantially with huge tables, since much fewer blocks of the table have to be read.
RAM
Generally, 2GB of physical RAM is just not enough to deal with billions of rows quickly. More RAM might go a long way - accompanied by adapted setting: obviously a bigger effective_cache_size to begin with.


DBCC checkDB stuck in killed/rollback state I killed DBCC CheckDB Job of 3TB Database which has been rolling back since 17 hours with 89 %.as i have checked that the SPID running with waittype EXECSYNC.
Could we have any other way to clear this mess without restarting SQL Server

Restarting a server while something is rolling back, is almost always a bad idea. You may make things worse.

You don't say if you are using one of the repair options with DBCC CHECKDB if you are, it can complicate things even more.

It has been a few hours since the original question was posted, so things have probably either self corrected or gotten worse.  Hopefully you have backups, that you will be able to use in the recovery, if things are not going well.

Some Related posts that may help

Why did a SQL Server Restart roll-back seemingly committed transactions?
SQL database stuck in recovery
KILLED/ROLLBACK query doesn't stop running
SQL Server 2008 R2 - Service shutdown - what with running stored procedures?
Query Execution Time


Latitude & longitude universal format I don't know yet what database I'll use in the future - MySql, Postgresql, Sqlite3 or anything else. At the moment I'm using a database N.

What's more or less universal approach for storing latitude & longitude? Varchar? Or big integer? Or float?

Should I store them in 2 columns or one?

Neither. The right answer is to use the GIS Spatial Type for a point. All of those databases have their own implementation.

PostgreSQL's PostGIS is easily the most full featured GIS implementation,

CREATE TABLE foo(
  gid  int GENERATED BY DEFAULT AS IDENTITY,
  geog geography(POINT)
);

I would highly suggest going PostgreSQL, and checking out the book PostGIS in Action if you're new to this.
See also,

My comparison of MySQL and PostgreSQL's PostGIS for spatial
MySQL Spatial Data Types (which suck btw),
SQLite Spatial Extension SpatiaLite


High MySQL "InnoDB Writes Per Second" - Can't see the queries? On our live MySQL 5.5 database server, we use MySQL Workbench for running manual queries, checking running queries to see what's going on, etc.

I noticed today that on the "Server Status" tab there's a very high number of "Queries per Second" and "InnoDB Writes per Second", yet on the tab which shows the list of "Client Connections", which shows all running queries, it is barely showing any queries.. perhaps 2 or 3 every few seconds.

What am I misunderstanding here? The two tabs don't add up at all.

EDIT - here's the output for SHOW ENGINE INNODB STATUS;

 --------------
 ROW OPERATIONS
 --------------
 0 queries inside InnoDB, 0 queries in queue
 2 read views open inside InnoDB
 Main thread process no. 24494, id 139631733794560, state: sleeping
 Number of rows inserted 36474186, updated 163927609, deleted 11892445, read      1747097889216
 1.06 inserts/s, 13.91 updates/s, 0.00 deletes/s, 33615.95 reads/s
 ----------------------------
 END OF INNODB MONITOR OUTPUT
 ============================

What you need is a better visual representation of the InnoDB Storage Engine

Think about the kind of things that trigger writes that can be monitored

Number of Write Operations

Innodb_dblwr_writes to the Double Write Buffer
Redo Logs

Innodb_os_log_writes and Innodb_os_log_pending_writes : Logical
Innodb_os_log_fsyncs and Innodb_os_log_pending_fsyncs : Physical

InnoDB_data_fsyncs and InnoDB_data_pending_fsyncs
Innodb_data_writes throughout the InnoDB Architecture

Bytes Written with InnoDB

Innodb_pages_written : NUmber of pages (data and index) written to InnoDB tables. Multiple this by 16384 (16KB) to get the number of bytes written to InnoDB tables.
Innodb_dblwr_pages_written Number of Double Write Pages Written. Multiple this by 16384 (16KB) to get the number of bytes written to the Double Write Buffer.
Innodb_os_log_written Bytes Written to the Redo Logs
Innodb_data_written Bytes written throughout InnoDB Architecture

These are among the things that are being churned inside and outside of InnoDB. Yet, this is not everything. Check the MySQL Documentation on the Status Variables.

My guess is that MySQL Workbench is just monitoring Innodb_data_writes.

If the data writes are high, given this

1.06 inserts/s, 13.91 updates/s, 0.00 deletes/s

which is about 15 queries per second, you may need to spend some time tuning InnoDB. Since InnoDB does fuzzy checkpointing, InnoDB will reveal a bottleneck in writes if you see constant writes that never abate. That could be due to the OS or VM. It could also be due to an undersized InnoDB Buffer Pool (I see it's 100% full). Lots of reads will bring in data and index pages into the Buffer Pool. New or updated data and index pages from the INSERTs and UPDATEs need to squeeze into the Buffer Pool as well. Perhaps a bigger Buffer Pool is in order.

From another angle, look at the number of rows accessed

     36474186 Rows from INSERTs
    163927609 Rows from UPDATEs
     11892445 Rows from DELETEs
    212294240 Rows Written
1747097889216 from SELECTs

When you divide 1747097889216 by 212294240, that means for every row you update, there are 8229.6 rows that are being read. This goes along with the 2245.46 (33165.95/14.97) SELECTs for every INSERT, UPDATE, or DELETE. I can see writes squeezing into the Buffer Pool. This makes me feel that the Buffer Pool, the Log Buffer, and the Redo Logs should all be increased.


Document database structure for auditors I am DBA for a Microsoft SQL Server 2008r2.
Due to an audition, I need to provide data structure of my database, to people without an explicit IT background.
I guess they would like the traditional format, with the tables as rectangles, with the fields listed inside, and the arrows between them to represent foreign keys.
Is this something that is provided out-of-the-box by Microsoft?

In SQL Server Management Studio Object Explorer, expand a database and look at "Database Diagrams" to create one.

This require db_owner rights which you should have as a DBA (a.k.a sysadmin)


Remove string after second specific character occures from left I have this type of values in table column

154646@8@486
45465@6464@654

etc.

How can i remove everything after second @ character ? I need to display

154646@8
45465@6464

I can do it only for all @ but not for second

SELECT REPLACE(LEFT('45@Tra@lala', CHARINDEX('@','45@Tra@lala')-1),'_',' ')

returns 45 but not 45@Tra

Thank you :-)

You can use the third parameter of charindex() that is used to specify where in the string the search will start.

declare @S varchar(20) = '45465@6464@654';
select left(@S, charindex('@', @S, charindex('@', @S)+1)-1);

Result

45465@6464


PostgreSQL: a Login Role to only access Views I need to write a SQL file which must do the following:

create a role called 'selection'
grant connect/usage to the current database
grant select/execute to all views of all schemas (or at least on the current)

I tried a massive amount of GRANT...ON...TO combinations but I can't figure it out.

Additional info: These scripts are executed by ANT with the database and schema name (search_path) already set. So I can not burn in names like 'production_db' or 'qa_db'.

The goal is to create a single PostgreSQL Role, so that clients can go around Tomcat (Hibernate) and run selections/searches directly on the Postgres server. The role must be able to select from Views but nothing else.

UPDATE: the accepted answer led me to a solution where I extracted all the views to a separate schema and granted access to that schema only.

Unfortunately that's not that easy because there is no "select any view" privilege (as e.g. Oracle offers).

If you can put all views into a single schema, then this could be achieved by granting the select privilege on that schema. Something like this:

First you need to create a role that can be used for this

create role view_reader;

The role only needs to be created once.

Then create a schema that contains all the views:

create schema view_holder;

Then allow view_reader to read everything in that schema:

grant select on all tables in schema report to view_reader;
alter default privileges in schema view_holder grant select on tables to view_reader;

The all tables is misleading because it also includes views - but also tables. That's the reason why you need to the views into a different schema than the tables. Because of the alter default privileges any new view created in that schema will automatically be accessible to the view_reader role.

You can now create a user that can select anything:

create user client password 'foobar';
alter user client set search_path = 'view_holder';
grant view_reader to client;

If client logs in, the default schema will be view_holder and thus a select * from some_view will default to the views in that schema. But as the user has only privileges on the views in that schema he cannot select from the underlying tables.

You can skip the intermediate role (view_reader) if you want and assign the privileges directly to client. But I think it's better better to give each client a separate user.

This should also work if the views in that schema simple select from the "real" views if you can't move the views into a different schema.

Note that this will increase the maintenance complexity for the views because unfortunately Postgres won't let you change the list of columns of a view if other views depend on it.


Use of Shrink after rebuilding Indexes in sql server 2005 I need to create a weekly database plan for my company's databases (5.8Gb), now I want to rebuild the Indexes, and then want to trigger a Shrink operation. Now I know Shrink creates  Index defragmentation, so can anyone guide me how to create this maintenance plan... Your suggestion will be very much valuable to me...Please

Paul Randal answers your question here:

  Data file shrink should never be  part of regular maintenance, and you
  should NEVER, NEVER have auto-shrink enabled. I tried to have it
  removed from the product for SQL 2005 and SQL 2008 when I was in a
  position to do so - the only reason it's still there is for backwards
  compatibility. Don't fall into the trap of having a maintenance plan
  that rebuilds all indexes and then tries to reclaim the space required
  to rebuild the indexes by running a shrink - that's a zero-sum game
  where all you do is generate a log of transaction log for no actual
  gain in performance.

  So what if you do need to run a shrink? For instance, if you've
  deleted a large proportion of a very large database and the database
  isn't likely to grow, or you need to empty a file before removing it?

  The method I like to recommend is as follows:

  Create a new filegroup
  Move all affected tables and indexes into the new filegroup using the CREATE INDEX ... WITH (DROP_EXISTING) ON  syntax, to move the tables and remove fragmentation from
  them at the same time
  Drop the old filegroup that you were going to shrink anyway (or shrink it way down if its the primary filegroup)


Oraagent many SYSRAC processes open I see in my 19c Cluster database a lot of sessions (over 350) with SYSRAC user and status INACTIVE with oraagent.bin TNS V1. They can consume my connection pool (how can I make this bigger number).
Why do I see so much those inactive connections and if this is realy so needed? Could this be some PL/SQL dbms monitoring metrics for the Oracle Cloud Control?
Kind regards.

These are internal, background processes connecting Oracle RAC Database with Oracle Grid Infrastructure High Availability Services. You can't control them. The status of "INACTIVE" can be misleading; it doesn't mean they don't have a purpose or aren't actually doing anything. Any background or user session will show as "INACTIVE" unless it is actually performing an operation that takes long enough to be recognized from one monitoring cycle to the next, at the exact moment you happen to query gv$session.
What "connection pool" are you trying to grow? These background processes should not count against most application-level pools or limits.


Mysql show no more than 5 concurrent connection I'm performing some load tests on my brand new server Apache/PHP/Mysql (Bitnami LAMP stack ). I'm using AWS with 1 EC2 instance behind a loadBalancer. At the moment I'm using loader.io for the tests. I'm testing a very simple API that perform this query:

select *,sleep(0.5) from debug limit 1

I added the sleep(0.5) because I wanted to see how the server behave with multiple concurrent connections and I found a bottleneck: if I run "SHOW PROCESSLIST" I can see only 5 process even if I have 10 concurrent user. The load test show that the connections are queued because the response time is growing during the test from 500 milliseconds to several seconds (depending on the duration of the test and the number of concurrent users).

I checked

select @@max_connections

and it's 151 (the default). max_user_connections is 0. What other parameter should I check to increase the number of concurrent connection on my DB?

If I run the test with 5 concurrent users, each one get a response in 500 milliseconds. If I add more concurrent users than the response time slow down.

If I run the load test on an API that does not access the DB there are no issues even with 400 concurrent users.

Monitoring with HTOP I see:

  Tasks: 34, 245 thr; 2 running

Could be here the issue?

Thanks a lot

The solution was actually very easy:

The Bitnami LAMP stack I'm using is configured with PHP-FPM, so the configuration is not on the apache side but in php/etc/common.conf

pm=ondemand
pm.max_children=5
pm.start_servers=2
pm.min_spare_servers=1
pm.max_spare_servers=3

inreasing the pm.max_children=5 solved the issue.


Inspecting the status of a long-running COPY statement I need to migrate a table from one PostgreSQL database to another. There was a chance I would need to fix some data, so I exported to a CSV. Then I imported the CSV in the second database with a COPY statement.

This process has been running for 5 days now. The only way I found to inspect its progress was to compare the sizes on disk. The original table was 95 GB (from psql's \dt+), and the CSV was 40 GB. So I thought I could compare the new table size with those numbers. I thought that the new table would stop at 95 GB, or even before. Instead, it's now at 103 GB and who knows when it will stop.

Of course, select count(*) does not work because the copy happens in its own transaction so the rows are shielded until it's done. But I know that the the table has about 1500 million rows. So if somehow I could get an estimate of the number of rows currently in the new table I could compare.

There is not yet a formal facility for monitoring the progress of a COPY operation.  You can use the pageinspect extension to get an estimate of the number of rows, even uncommitted ones.  Assuming the existence of the table has been committed, and the table was empty other than the in-progress COPY, then you could use:

select sum(
        (select count(*) from
          heap_page_items(get_raw_page('pgbench_accounts', x))
        )
    )
from generate_series(0, (pg_relation_size('pgbench_accounts')/8192)::int-1) as f(x);

(This still assumes you are using the default blocksize of 8192.)


Log large queries with PGbouncer Is there a way to ask pgbouncer to log any query that generates a result set larger than, say, 5MB?

In short - no

You can show stats; to get the total_received and total_sent per database. Assuming you can check the stats before query and after, calculating the difference won't give you the query result size. Even in ideal env, when no other parallel sessions exist. Eg:

t=# copy (select id from t) to '/tmp/1';
COPY 1043482
t=# \! du -h /tmp/1
7.0M    /tmp/1

checking received:

t=# select 40493353-40493297;
 ?column?
----------
       56
(1 row)

hm, so the size of a query result is 7MB, but received bytes are 56. Ah! I saved result of query on server! So pgbouncer technically indeed did not receive large set of data, ok - do it to client:

t=# \copy (select objectid from pond_user) to '/tmp/1';
COPY 1043482
t=# select 40493442-40493353;
 ?column?
----------
       89
(1 row)

Same story... Maybe lets check bytes sent?..

t# select (355612622-343149820)/(1024*1024);
 ?column?
----------
       11
(1 row)

11MB... Not particularly precise match.

SO:

pgbouncer does not log the result set size, You can use show stats assuming you can isolate sessions in time and your estimations are to get a very rough approximation for sizes.

also

People are using iptraf or such for the same, Somebody logs long taking queries with log_min_duration_statement and then repeat them with CREATE TABLE AS to get the size, some even write patches for client.

even also

Analytic approach here leads to even more complications:

https://stackoverflow.com/questions/13570613/making-sense-of-postgres-row-sizes

Measure the size of a PostgreSQL table row

Although mycrosoft and mysql offers such approximation in cosy way


How to get past the 5000 row limit in a SQL Server Reporting Services tablix report? I have a report whose data returned exceeds 5000 rows in a tablix in SSRS 2012. When I Google this, I get all kinds of info concerning settings in Sharepoint, but this instance is running in Native mode, so Sharepoint is not involved. There is a List View Threshold setting in Sharepoint, but I can't find a reference to this in SSRS only. Also, I would prefer to increase or disable this limit per report and not for the whole instance. Is this possible? Or does SSRS simply have a hard limit of 5000 rows?

This turned out to have nothing to do with the Microsoft side at all. The data source we are using is a linked server to Proficy Historian and, by default, only pulls 5000 rows. You have to explicitly tell it either how many rows you want to pull, or tell it RowCount = 0 for unlimited rows. Sorry for the noise!

How To Set Rowcount in Historian OLE DB SQL Query


DBMS_SCHEDULER JOB interval not working I've created DBMS_SCHEDULER job, which type is CHAIN. Chain has 2 steps and 2 rules. Each step has PL/SQL "program" (which inserts SYSDATE to table). First rule's condition is TRUE. Second rule has condition checks if step 1 is completed and then runs second step. After I enabled CHAIN and JOB, then DBA_SCHEDULER_JOB_RUN_DETAILS table shows SUCCEEDED for both chain steps. Table keeping date has new values. So I would say that chain has ran successfully.

Problem here is that JOB does not execute second time. Job is suppose to run every 5 minutes.

DBMS_SCHEDULER.CREATE_JOB (
    job_name          => 'RUN_REFRESH',
    job_type          => 'CHAIN',
    job_action        => 'QUICK_REFRESH_CHAIN',
    repeat_interval   => 'FREQ=MINUTELY;INTERVAL=5',
    start_date       => SYSTIMESTAMP,
    enabled      => TRUE
);
END;
/

Job is also active in DBA_SCHEDULER_RUNNING_JOBS table.

Additional info:

SELECT START_DATE, LAST_START_DATE, NEXT_RUN_DATE
FROM DBA_SCHEDULER_JOBS
WHERE job_name = 'RUN_REFRESH';

shows:
21.01.2015 12:58:18,000000000
21.01.2015 12:58:18,328107000
21.01.2015 12:58:18,000000000

Questions:

Does DBA_SCHEDULER_RUNNING_JOBS show only currently running
jobs instead or enabled/scheduled jobs?
Why doesn't JOB run second time, even though CHAIN ran
successfully?

If a job has to run in an interval, the job needs to be in the state 'SCHEDULED' in between. In case of a job chain, make sure that it actually has an ending, otherwise it will remain running forever.

DBA_RUNNING_JOBS only shows the jobs that are in state 'RUNNING'
make sure the chain has an END action.


SQL Server 2008 Full Text Index Never Seems to Complete Our website has a SQL Server 2008 R2 Express Edition database with full-text indexing for our website search.  Each time a new record is added or updated in one of the indexed tables, the indexing process never seems to complete.

I have been monitoring the status over the last several weeks using basically the same query found on this site: http://www.sqlmonster.com/Uwe/Forum.aspx/sql-server-search/2155/Why-is-this-population-taking-so-long

This is what I see when I run the query (click for full size):

The newest records in the indexed tables never complete and are not searchable. Even though there is not very much data in the tables, I have waited days to see if the indexing completes, but nothing ever changes.

The only way I am able to get the indexing to complete successfully is to either rebuild the catalog or to drop and re-create all of the indexes.

Every time I have done that, the same problem ends up coming back as soon as the first new record is added.

Here are the server stats just in case:

Quad-Core AMD Opteron 2.34GHz
4GB RAM
Windows Server 2008 R2 Enterprise SP1 x64
SQL Server 2008 R2 Express Edition with Advanced Services x64

I finally found the cause of my problem!

I tried for months to track down the issue, but finally gave up, disabled automatic change tracking, just manually initiated incremental population and moved on with my life.

Meanwhile, there had been another nagging error that I was having trouble tracking down.  Periodically the website would throw a DB connection error:

  Cannot open database "XXXX" requested by the login. The login failed. Login failed for user 'XXXX'.

It turned out that both of these problems had the same solution.  All I had to do is turn off a database setting called Auto Close.  To do this, just right-click the database and click properties.  In the properties window select Options and set "Auto Close" to false.

As soon as I disabled Auto Close, my DB login issues went away and the automatic change tracking worked perfectly.

Thanks again for everyone's help.  I appreciate it!


encryption at rest for mysql A MySQL database needs to contain highly sensitive data that cannot be left unencrypted at rest.  But yet the database needs to remain searchable by an app.  What are the options for encryption at rest with MySQL?

I have looked into CryptDB but it has not been supported since early 2014, and CryptDB also does not integrate with Java naturally.  Might be a lot of work to get CryptDB to work with Spring and eclipse, only to find that it is less and less supported as time passes by.  And it seems like other row-level encryption methods are not yet viable, unless someone has suggestions.

So what are the database level encryption options for MySQL?  And what are the file system encryption options related to a MySQL database running in CentOS 7?

The MySQL database interacts with a Spring MVC app exclusively.

Note: This is running on a dedicated physical box that ONLY has CentOS 7 installed.  So we cannot use any Windows-specific tools.

If you have MYSQL installed and running on a Windows Server, you may want to look into BitLocker with TPM encryption plus PIN boot password. Even if the host OS is Windows but the CentOS is a VM, then this would suffice.

TrueCrypt
VeraCrypt

There's data-at-rest encryption so when it's at rest on a server disk partition not being accessed by some query, then using OS level full disk encryption such as BitLocker with TPM plus PIN boot password is fine per many regulations such as HITECH, HIPAA, etc.

Data-in-motion and Access Controls of who or what applications you allow to access the data is a different story but I'd look into something of that nature or equivalent for data-at-rest encryption specifically.