Is it good sign? If SQL Server Buffer and Procedure cache hit ratio always showing 100% If 100% Buffer and Procedure cache hit ratio is considered not good in general?

I check Buffer Cache hit ratio with below query and it showing 100% (100.0000000000000)

SELECT (a.cntr_value * 1.0 / b.cntr_value) * 100.0 as BufferCacheHitRatio
FROM sys.dm_os_performance_counters  a
JOIN  (SELECT cntr_value,OBJECT_NAME
    FROM sys.dm_os_performance_counters
    WHERE counter_name = 'Buffer cache hit ratio base'
        AND OBJECT_NAME = 'SQLServer:Buffer Manager') b ON  a.OBJECT_NAME = b.OBJECT_NAME
WHERE a.counter_name = 'Buffer cache hit ratio'
AND a.OBJECT_NAME = 'SQLServer:Buffer Manager'

Are there instances that it is actually bad and can contribute to server performance degradation? I want to know how much of Buffer and Procedure Cache hit ratio is reasonable to have normal SQL server?

If 100% Buffer and Procedure cache hit ratio is considered not good in general?

You should not ONLY rely on buffer cache hit ratio it does not accurately measures or give signs of memory pressure the reason has been shown by Jonathan Kehayias in this Blog. The reason is concept called as Read Ahead in SQl Server.

Due to read aheads more than required pages are brought into memory every time a single scatter gather read happens and this mostly keeps buffer pool populated with pages giving indication that their is no memory pressure due to churning out of pages. If you read the blog you would find other important counters to look for memory pressure.

  The read-ahead mechanism allows the Database Engine to read up to 64 contiguous pages (512KB) from one file. The read is performed as a single scatter-gather read to the appropriate number of (probably non-contiguous) buffers in the buffer cache. If any of the pages in the range are already present in the buffer cache, the corresponding page from the read will be discarded when the read completes. The range of pages may also be "trimmed" from either end if the corresponding pages are already present in the cache.

One cannot accurately say SQL Server has sufficient memory or not by just looking at BCHR, there are other counters to be taken into account


How can I group two joined tables How can I group (by) two joined tables (in an Oracle database) like this:

cant:
id      CANT_VAL    COD_VAL fk_id_chest
18059   18.56       R12     1
18060   11          R10     2
18061   15          R11     3

col:
FK_ID_COL_DMA   CANT_VAL    COD_VAL
18059           1134        R10
18059           1234        R3
18061           1111        R5

to look like that:

id      CANT_VAL    COD_VAL    fk_id_chest
18059   18.56       R12        1
18059   1134        R10        1
18059   1234        R3         1
18060   11          R10        2
18061   15          R11        3
18061   1111        R5         3

fk_id_chest is a "link" to another table that I need in the final result.
Thanks!

I found my answer, thanks!

select cant.id,CANT.CANT_VAL,CANT.COD_VAL,CANT.FK_ID_CHEST from cant
union
select COL.FK_ID_COL_DMA,COL.CANT_VAL,COL.COD_VAL,CANT.FK_ID_CHEST from col
    inner join cant
        on cant.id=COL.FK_ID_COL_DMA


Cannot add or change records I'm designing a database structure wherein I like to apply the concept of inheritance.
There's a base tables and derived tables in which the relationship is one-to-one. I'm quite new as of how could this be achieved and how the data are to be fed to the tables because I'm getting errors I can't solved.

I'm wondering if I'm on the right track doing this.

Here's the design view of the database:

I get the error of

You cannot add or change a record because a related record is required in the table **PatientRegular**

In which case the table I'm on currently working?

And the relationship view:

What is the right way to do that? I want to inherit all those similar fields to another table?

Update

Either way, I cannot also insert new data in the base table (I must be in a wrong track)

In this scenario, as James Anderson suggests, one-to-one relationships are not the way to do it. One patient may be checked up, operated on, discharged etc., more than once. The tables you have now should be renamed as follows and given their own unique ID primary keys to better represent what they are trying to model:

PatientRegular to Regular
PatientDischarge to Discharge
PatientOperate to Operation
PatientCheckup to Checkup

They should still retain their PatientID columns but those columns should become foreign key reference columns back to the PatientID column in the Patient table. In other words, you will have one-to-many relationships between:

Patient to Regular
Patient to Discharge
Patient to Operation
Patient to Checkup


Error "ORA-0091:Invalid Character" when executing a PL-SQL statement in Oracle I'm new to oracle pl-sql and I'm facing error ORA-0091:Invalid character when executing this query below. But when I comment the Execute Immediate statement and use dbms_output.put_line the result of the variable qry is fine and there is no problem with the query inside qry variable.

declare
qry                  varchar2(5000);
effdate              date := to_date('5/19/2020', 'mm/dd/yyyy');
user_id              varchar2(1000) := 'pantea';
search_number        varchar2(50)   := '8080';
deposit_filter_query varchar2(5000) := '  deposit_group_code =  -1
                                    and deposit_group_desc =  -1
                                    and currency_desc      = ''-1''
                                    group by customer_num
                                    having sum(dep_avg_balance) > 1;';

begin

  qry := 'insert into dep_filter_cust
           select distinct customer_num ,''' || search_number || '''
              from vmi_customer_deposit_detail v
                inner join branchlf_user b
              on b.userid = ''' || user_id || '''
                and dep_branch_cod = v.dep_branch_cod
           where effective_date = to_date(''' ||
             to_char(effdate, 'mm/dd/yyyy') || ''','' mm/dd/yyyy'') and ' ||
              deposit_filter_query;

  --dbms_output.put_line(qry);
  execute immediate to_char(qry);
         commit;

 end;

I was wondering if you could help me finding the problem here . Thanks in advance.

As mentioned in the comments, your original problem is the unnecessary semicolon ( ; ).

Code Review

By building the query the way you did, you are introducing SQL Injection Vulnerability. (obligatory XKCD reference).  To prevent SQL Injections, use BIND variables.

Most RDBMS allow you to use ? as a place holder.  Oracle supports this and Named place holders (eg :SEARCH_NUM ).  By using a Bind variable, DATE data type will be bound as DATEs, etc.  This removes the necessity for TO_CHAR/TO_NUMBER.

Since your code is really dynamic, I highly advise using the DBMS_SQL package to parse your SQL statement and Bind the variables.  This is especially true if the usage of Binds will be dynamic also. (eg use two variables one time, use 3 another, etc.)

Documentation for DBMS_SQL can be seen here.

Example code:

declare
  qry                  varchar2(5000);
  effdate              date := to_date('5/19/2020', 'mm/dd/yyyy');
  user_id              varchar2(1000) := 'pantea';
  search_number        varchar2(50)   := '8080';
  deposit_filter_query varchar2(5000) := '  deposit_group_code =  -1
                                      and deposit_group_desc =  -1
                                      and currency_desc      = ''-1''
                                      group by customer_num
                                      having sum(dep_avg_balance) > 1'; -- removed semi-colon
  initial_dml           varchar2(5000) := 'insert into dep_filter_cust (col1, col2)' || chr(10) -- alway define the columns in INSERT
             || 'select distinct customer_num , :SEARCH_NUM
                from vmi_customer_deposit_detail v
                  inner join branchlf_user b
                on b.userid = :USER_ID
                  and dep_branch_cod = v.dep_branch_cod
             where effective_date = :EFFDATE' || CHR(10); -- chr(10) === \n; NEWLINE character for visualisation

actual_sql  varchar2(32767);

c   pls_integer;
ret PLS_INTEGER;

begin

  -- build your SQL (I do this in a separate function)
  actual_sql := initial_dml || ' and ' || deposit_filter_query;

  -- debug results
  dbms_output.put_line( actual_sql );

  -- get your cursor
  c := dbms_sql.open_cursor();

  -- parse the SQL
  dbms_sql.parse( c, actual_sql, DBMS_SQL.NATIVE );

  -- bind variables (this can be dynamic if needed)
  -- Oracle can use NAMED Binds
  dbms_sql.bind_variable( c, ':SEARCH_NUM', search_number );
  dbms_sql.bind_variable( c, ':USER_ID', user_id );
  dbms_sql.bind_variable( c, ':EFFDATE', effdate );

  -- execute
  ret := dbms_sql.execute( c );

  -- close the cursor
  dbms_sql.close_cursor( c );
end;
/

If you don't do that, at the very least, use the USING clause of EXECUTE IMMEDIATE.

eg

execute immediate actual_sql using search_number, user_id, effdate;


Update a Column with sequential number based on the order by of a column in Oracle Sql I have table with below create scripts

create table training(
 action_id       NUMBER not null,
 training_id       NUMBER,
 course_name       VARCHAR2(100),
 display_order     NUMBER default 0

)

  Below is the data

ACTION_ID   TRAINING_ID DISPLAY_ORDER
 675150      770650       0
 675150      780700       0
 675150      780701       0
 675180      770550       0
 675180      780800       0
 675180      780801       0

  I need write a db script that will update  DISPLAY_ORDER so that it
  gets order from 1 order by  TRAINING_ID   for  a particular   ACTION_ID

  So outcome will be

ACTION_ID   TRAINING_ID DISPLAY_ORDER
 675150      770650       1
 675150      780700       2
 675150      780701       3
 675180      770550       1
 675180      780800       2
 675180      780801       3

create table training(
 action_id       NUMBER not null,
 training_id       NUMBER,
 course_name       VARCHAR2(100),
 display_order     NUMBER default 0
);

insert into training (action_id, training_id) values (675150, 770650);
insert into training (action_id, training_id) values (675150, 780700);
insert into training (action_id, training_id) values (675150, 780701);
insert into training (action_id, training_id) values (675180, 770550);
insert into training (action_id, training_id) values (675180, 780800);
insert into training (action_id, training_id) values (675180, 780801);
commit;

select action_id, training_id, display_order from training;

 ACTION_ID TRAINING_ID DISPLAY_ORDER
---------- ----------- -------------
    675150      770650             0
    675150      780700             0
    675150      780701             0
    675180      770550             0
    675180      780800             0
    675180      780801             0

Then with some magic:

merge into training t using
(select action_id, training_id, row_number() over (partition by action_id order by training_id)
  as display_order
from training) t2
on (t.action_id = t2.action_id and t.training_id = t2.training_id)
when matched then update set t.display_order = t2.display_order;
commit;

select action_id, training_id, display_order from training;

 ACTION_ID TRAINING_ID DISPLAY_ORDER
---------- ----------- -------------
    675150      770650             1
    675150      780700             2
    675150      780701             3
    675180      770550             1
    675180      780800             2
    675180      780801             3


Slow delete with varbinary(max) column I have two tables that temporarily hold uploaded data from a web application before it gets processed. This is running on an Azure SQL Database.

UploadFile

Id uniqueidentifier
CustomerId uniqueidentifier
FileName nvarchar(MAX) NULL
UploadDate datetime
UploadedBy nvarchar(MAX) NULL

UploadFileChunk

Id uniqueidentifier
Data varbinary(MAX) NULL
UploadFileId uniqueidentifier
[Index] int

UploadFile has a foreign key with cascade delete to UploadFileChunk. UploadFileChunk has a non-clustered index on UploadFileId. I have been uploading chunks of 1MB each.

Inserting and reading this data is working really well, but deleting a record from UploadFile after its data has been processed is really slow. On an S0 10 DTU testing Azure environment, it took 38 minutes to delete 12 records with ~500 child chunks collectively (just an example - it also performs poorly on our higher powered production environment and a fast local machine.)

How do I make it fast?

There is an index on UploadFileChunk.FileUploadId. Here's the execution plan.

I ran the following query (from Finding Blocking Queries in SQL Azure) several times while the slow delete was mid-execution and it didn't return any results:

SELECT TOP 10
    r.session_id,
    r.plan_handle,
    r.sql_handle,
    r.request_id,
    r.start_time,
    r.status,
    r.command,
    r.database_id,
    r.user_id,
    r.wait_type,
    r.wait_time,
    r.last_wait_type,
    r.wait_resource,
    r.total_elapsed_time,
    r.cpu_time,
    r.transaction_isolation_level,
    r.row_count,
    st.text
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS st
WHERE
    r.blocking_session_id = 0
    AND r.session_id IN
    (
        SELECT DISTINCT (blocking_session_id) FROM sys.dm_exec_requests)
GROUP BY
    r.session_id,
    r.plan_handle,
    r.sql_handle,
    r.request_id,
    r.start_time,
    r.status,
    r.command,
    r.database_id,
    r.user_id,
    r.wait_type,
    r.wait_time,
    r.last_wait_type,
    r.wait_resource,
    r.total_elapsed_time,
    r.cpu_time,
    r.transaction_isolation_level,
    r.row_count,
    st.text
ORDER BY
    r.total_elapsed_time DESC;

Truncating might be faster [than deleting] but these two tables contain data for multiple files and I must be able to delete one at a time. The nvarchar(max) columns could probably be something else, this is just what Entity Framework generated based on our domain classes. Do you think that would help with this problem?

I confirmed that deleting records directly from FileUploadChunks is just as slow. On my extra slow example (S0 10 DTU) it's around 1 second per row deleted.

I've just concluded a week of back and forth with Microsoft's gold partner support. They have confirmed that this situation is not getting any better and that these slow deletes are the normal behaviour of SQL Azure/2016.


Mysql Server optimization tips needed! (InnoDB) I've recently been trying to optimize our mysql server but I've encountered some pretty serious problems on the way. We're running a game server which is coded in C#. We have two dedicated servers. The game server is connecting our dedicated mysql server which is located in the same network. It's using mysql connection pooling. Current settings for the pooling are:

  minsize=80
  maxsize=400

We've about 300-400 threads connected on peaktime and most of our queries (~70%) are usually updates/inserts. So most of the tables are set to InnoDB. Sometimes we get some massive lag and the game server says that it lost the connection to the mysql server. So we are now trying to figure out is there something wrong with our mysql server configuration. The reason why It's now showing more reads is because our game server hasn't been working properly due mysql problems.

Here's our current my.cnf:
http://pastebin.com/dF0a5ubR

And here's "show engine InnoDB status" output:
http://pastebin.com/fmmad02q

Mysqltuner:
http://pastebin.com/pDTXSGTj

tuning-primer:
http://pastebin.com/89PVGqyC

Our mysql server:

Ram 16gb
Cpu Intel(R) Core(TM) i5-2500 CPU @ 3.30GHz
OS Linux Debian 64-bit
120GB SSD

I'm in need of some suggestions on how to improve our mysql server configuration.

A few suggestions:

You might want to increase the size of your log files.  They are currently set to 250MB a piece.  A larger value there (perhaps a gig or two) should help reduce IO a bit.
Turn off the slow query logging
Decrease the buffer pool size a bit (or get more memory).  You might be starving threads for memory by using everything for the buffer pool.


PostgresSQL: Get single attribute of UDT in SELECT statement I created a user-defined type in a PostgreSQL 9.2 database and am trying in the SELECT statement to get only an attribute of the UDT. However, I don't seem to get my code to work.

Creation of type and table:

CREATE TYPE  ttp AS (f1 int, f2 int);
CREATE TABLE tbl (ctyp ttp);

The SELECT statement:

-- does not work
SELECT ctyp.f1 FROM tbl
SELECT ctyp(f1) FROM testtable

Both times I get an error. Is it possible to access a single attribute defined in a UDT in a SELECT?

Use:

SELECT (ctyp).f1 FROM tbl;

The parentheses are necessary to disambiguate tables from composite types as detailed in the manual on composite types.


Stopping multiple server side SQL Server traces immediately A mistake in an audit script generated more than 100 traces. Is there a way I can stop all of them?

Below are a couple of T-SQL examples that will stop and delete server-side SQL traces or Extended Event traces you can customize for your particular situation.

SQL Trace:

DECLARE @sql nvarchar(MAX) = (
    SELECT 'EXEC sp_trace_setstatus ' + CAST(id AS varchar(10)) + ', 0;
EXEC sp_trace_setstatus ' + CAST(id AS varchar(10)) + ', 2;
'
    FROM sys.traces
    WHERE path LIKE '%\yourtracefilenamepattern%.trc'
    ORDER BY id
    FOR XML PATH(''), TYPE).value('.', 'nvarchar(MAX)');
PRINT @sql;
--EXEC(@sql);

Extended Event trace:

DECLARE @sql nvarchar(MAX) = (
    SELECT 'ALTER EVENT SESSION ' + QUOTENAME(name) + ' ON SERVER STATE = STOP;
DROP EVENT SESSION ' + QUOTENAME(name) + ' ON SERVER;
'
    FROM sys.dm_xe_sessions
    WHERE name LIKE '%yourtracenamepattern%'
    ORDER BY name
    FOR XML PATH(''), TYPE).value('.', 'nvarchar(MAX)');
PRINT @sql;
--EXEC (@sql);


Use "NOT IN" in the same table, Mysql I have a table like that:

table1
    +----+------+--------------+
    | id | name | phone_number |
    +----+------+--------------+
    | 1  |  A   | 111111111111 |
    +----+------+--------------+
    | 2  |  B   | 222222222222 |
    +----+------+--------------+
    | 3  |  C   | 222222222222 |
    +----+------+--------------+
    | 4  |  D   | 111111111111 |
    +----+------+--------------+
    | 5  |  E   | 222222222222 |
    +----+------+--------------+

I want to keep only 1 of the same phone number, don't care name. So, the table should be:

table1
    +----+------+--------------+
    | id | name | phone_number |
    +----+------+--------------+
    | 1  |  A   | 111111111111 |
    +----+------+--------------+
    | 2  |  C   | 222222222222 |
    +----+------+--------------+

I tried many ways, many hrs to delete or select but doesn't work, I did use code to resolve this, but I will appreciate for any explanation, thank you.

DELETE FROM table1
where id NOT IN (SELECT id FROM table1 group by phone)

always said: You can't specify target table 'table1' for update in FROM clause

SELECT *
FROM table1
where id NOT IN (SELECT id FROM table1 group by phone)

always return an empty result.


Where is sp_MSrepl_getpublisherinfo in SQL Server? In learning about replication in SQL Server and having problems creating a subscription, I looked at the definition for sp_addsubscription. It calls sys.sp_MSrepl_getpublisherinfo, which I cannot find on the server. This stored procedure obviously exists, as it no errors occur regarding it not existing. Where does it live, and can its code be viewed?

So after more research I found that sys.sp_MSrepl_getpublisherinfo lives in a database named mssqlsystemresource, which lives in Sql Server's binn folder. This database is invisible to SSMS, but you can copy and attach it as a different name to have full access. This process is described here:

http://www.sqlnewsgroups.net/sqlserver/t21348-browse-mssqlsystemresource-mdf-sql-servers-resource-database.aspx

I have copied that text here, in case that page goes away in the future:

  Giving credit where credit is due, I found the following info I am
  about to  post at the following location:

  http://web.archive.org/web/20150407042809/http://www.aspfaq.com/sql2005/show.asp?id=28

  Since the engine has hooks that hide the mssqlsystemresource database
  from  users, you don't have direct access to it through the GUI.

  However, there is a way around this:

  Determine where the system databse files live, and keep this path handy:

USE master
GO
SELECT REPLACE(filename, 'master.mdf', '')
  FROM sys.sysfiles
 WHERE fileid = 1

  Stop the SQL Server service;
  Copy the files mssqlsystemresource.*df -> resource_copy.*df.  NOTE: do not rename or remove the mssqlsystemresource files!
  Start the SQL Server service;
  Run the following code in a new query window:

EXEC sp_attach_db
  'Resource_Copy',
  '<path from above>resource_copy.mdf',
  '<path from above>resource_copy.ldf'

  Now, the system will no longer identify this database as a "special"  database
  You can easily scan through the names of system objects that might  otherwise be unknown (and browse any code associated with these
  objects) from  a GUI (like SQL Server Management Studio).


How to terminate a suspended SQL Server transaction waiting for IO_COMPLETION? We have a transaction that has been running for more than 5 hours. We are running out of disk space. The session has been killed but it is still waiting for IO_COMPLETION. Actually the wait_type just changed to PAGEIOLATCH_EX. How can I terminate the suspended SQL Server transaction? I am not worried about losing data since all of it can be repopulated.

session_id: 54
STATUS: suspended
blocked by: 0
wait_type: PAGEIOLATCH_EX
Elapsed Time (in Sec): 19750.420000
open_transaction_count: 2

Next time this happens, run sp_WhoIsActive (download / documentation) and see who is running what and look over the logic. Check to see if the TSQL can be optimized to run quicker or perhaps chop it up into smaller transactions.

I've had cases where the transaction log of a bad query by a report writers, data loaders, etc. would grow the transaction log larger than the data file size, and it's usually a bad performing, poorly written query that's not optimized or chunked into smaller transactions to give back free space once the transaction completes - this was on a SIMPLE recovery model database too by the way - FULL recovery model databases would need to have transaction log backups completed to enable reuse of transaction log space of committed transactions.

The root problem is likely the query so determining who is doing what and reaching out to them and reporting the issue with your findings would put the pressure on them to fix their logic to not hose up the server disk space for that disk partition - hopefully it's not your logic but if it is, look at optimizing the logic to query tune for performance.


How restore from ASM to a new server? We have a machine that crashed completely.
The ASM disks are fine. Is it possible to mount the old ASM disks to a new machine and then restore the datafiles/redo files/ control files/everything to the new database?

Please note I don't want to restore from backup. I need to restore everything on ASM disks to new hardware with fresh OS and fresh database installation.
Is that possible? if yes, how?

We are running Oracle 11gR1 database on Redhat Linux.

You can use RMAN for this.
There is some documentation I suggest you read in order to accomplish this easy way:

http://docs.oracle.com/cd/E11882_01/server.112/e18951/asm_rman.htm


Get physical disk id and drive letters from management studio? Can anyone tell me how I can go about getting the physical disk id and drive letters of the local server from within management studio?  I know I can grab the drive letters with xp_fixed drives and extrapolate to the full disk size with xp_cmdshell and fsutil, but what I want is something like the below:

╔══════════╦═════════════╦════════╦════════════╗
║ Drive_id ║ Dive Letter ║  Size  ║ Free_Space ║
╠══════════╬═════════════╬════════╬════════════╣
║        0 ║ C           ║  10240 ║       8000 ║
║        0 ║ E           ║ 102400 ║      25000 ║
║        1 ║ F           ║ 102400 ║      22000 ║
╚══════════╩═════════════╩════════╩════════════╝

Any ideas?

EDIT:
Just to be clear I have all of the pieces of the puzzle in the below code EXCEPT the physical drive_id.  If anyone can think of a way to add it to the code below that would be fantastic - if I need to blow all that away and redo it to get the information I'm after I can live with that too.

Code So Far:

SET NOCOUNT ON
IF OBJECT_ID(N'tempdb..#FreeDiskSize') IS NOT NULL DROP TABLE #FreeDiskSize

CREATE TABLE #FreeDiskSize (name varchar(5), available_mb int)

INSERT #FreeDiskSize(name,available_mb)
EXEC master..XP_FIXEDDRIVES
DECLARE @Drive VARCHAR(5), @CMD VARCHAR(1000), @pos SMALLINT

IF OBJECT_ID(N'tempdb..#TotalDiskSize') IS NOT NULL DROP TABLE #TotalDiskSize

CREATE TABLE #TotalDiskSize (TotalBytes VARCHAR(1000), Drive VARCHAR(5))

DECLARE Drive_name CURSOR FOR

SELECT name FROM #FreeDiskSize
OPEN Drive_name
FETCH NEXT FROM Drive_name INTO @Drive
WHILE @@FETCH_STATUS = 0
BEGIN
SET @CMD='MASTER..XP_CMDSHELL ' + ''''+ 'FSUTIL VOLUME DISKFREE ' + @Drive + ':| find '+ '"Total # of bytes"'+''''
INSERT #TotalDiskSize(TotalBytes) EXEC (@CMD)
UPDATE #TotalDiskSize SET Drive=@Drive WHERE Drive IS NULL
FETCH NEXT FROM Drive_name INTO @Drive
END
CLOSE Drive_name
DEALLOCATE Drive_name
DELETE FROM #TotalDiskSize WHERE TotalBytes IS NULL
SELECT @pos=charindex(':',TotalBytes) FROM #TotalDiskSize

SELECT b.Drive as Drive, CONVERT(BIGINT,(RIGHT(b.TotalBytes,(LEN(b.TotalBytes)-@pos))))/1073741824 AS TOTAL_Drive_SPACE_GB, a.available_mb/1024 AS AVAILABLE_SPACE_GB
FROM #FreeDiskSize a WITH (NOLOCK)
INNER JOIN #TotalDiskSize b WITH (NOLOCK)ON a.name=b.Drive

You can use glennberry scripts..

 SELECT DISTINCT vs.volume_mount_point,
vs.file_system_type,
    vs.logical_volume_name,
 CONVERT(DECIMAL(18,2),vs.total_bytes/1073741824.0) AS [Total Size (GB)],
    CONVERT(DECIMAL(18,2),vs.available_bytes/1073741824.0) AS [Available Size (GB)],
    CAST(CAST(vs.available_bytes AS FLOAT)/ CAST(vs.total_bytes AS FLOAT) AS DECIMAL(18,2)) * 100 AS [Space Free %]
    FROM sys.master_files AS f WITH (NOLOCK)
    CROSS APPLY sys.dm_os_volume_stats(f.database_id, f.[file_id]) AS vs OPTION (RECOMPILE);

Below is the sample output,is this what you need..


SQL2019 CU4 PWAIT_EXTENSIBILITY_CLEANUP_TASK I'm running SQL2019 CU4 Linux and seeing a high number of PWAIT_EXTENSIBILITY_CLEANUP_TASK. Can anyone share their insight on what this wait type means and how we can reduce it?

Select * from sys.dm_os_wait_stats
WHERE
wait_time_ms<>0
and wait_type='PWAIT_EXTENSIBILITY_CLEANUP_TASK'
order by signal_wait_time_ms desc

Looks like a background task; those usually run all the time, so they generate high waits but they're benign and there's not really much you can do about them (and you'll find this wait is filtered out of the popular diagnostic scripts out there).

Questions to ask yourself, when investigating a wait you can't get much information on, there's usually a reason (it's obscure because it's a nothin-burger).

Do you see an actual problem associated with this wait?
Do you see any actual sessions waiting on this (check sys.dm_exec_session_stats, but I doubt you'll find anything, because these types of waits aren't waits users wait on).

You'll always have a highest wait even if your system is completely idle, it doesn't mean the highest wait is a problem.

This post might be worth a read, but the TL;DR is that you're always going to have waits and the highest wait isn't necessarily a problem you need to solve. The analogy I use when teaching is that you're always going to have a worst employee, but you can't constantly fire your worst employee for very long.

FWIW I also see this as one of my top waits, but like SP_SERVER_DIAGNOSTICS_SLEEP, XE_TIMER_EVENT, and SOS_WORK_DISPATCHER, I'm not panicking. When I see evidence that there is a performance problem and I can tie it to this wait (or resources that are congruent), I'll dig.


Should you use the schema name in WHERE, ON, and other clauses? I know it is good practice, and a performance boost, in SQL Server to prefix the table name with the schema (dbo.TableName) in the FROM clause of a SELECT statement but should you also use it on other clauses like the WHERE or ON? Should you also specify the TABLE name for columns in the where clause if the column name is unique?

Take two scenarios:

SELECT Col1, Col2, Col3
FROM dbo.Table
WHERE Col1 = Value;

and

SELECT Col1, Col2, Col3,
FROM dbo.Table
INNER JOIN dbo.Table2
ON Col1 = Col5
WHERE Col6 = Value;

In the first scenario should I add dbo.Table to each column name?

IN the second scenario if I added an alias to each table would that automatically include the schema data too or would I still need to pre-fix the column names with the schema.

And in both scenarios do I even NEED to prefix the columns with schema data or is that pointless and gives no performance boost at all?

SELECT Col1, Col2, Col3
FROM dbo.Table
WHERE Col1 = Value;

  In the first scenario should I add dbo.Table to each column name?

For single tables, table aliases are OK to be excluded.

SELECT Col1, Col2, Col3,
FROM dbo.Table
INNER JOIN dbo.Table2
ON Col1 = Col5
WHERE Col6 = Value;

  IN the second scenario if I added an alias to each table would that automatically include the schema data too or would I still need to pre-fix the column names with the schema.

Table alias will include the FULLY qualified table name.

e.g. dbo.Table as T1 will reference the dbo.Table.

  And in both scenarios do I even NEED to prefix the columns with schema data or is that pointless and gives no performance boost at all?

First your query example is wrong. It should be (carefully see the aliases I have used, else it will be syntactically wrong - SQL Server will throw errors).

 SELECT T1.Col1, T1.Col2, T2.Col3,
    FROM dbo.Table T1
    INNER JOIN dbo.Table2 T2
    ON T1.Col1 =  T2.Col5
    WHERE T2Col6 = Value;

Hopefully, the above query will answer your 3rd question as well.


Amazon Postgres RDS: Update pg_attribute to update my custom attribute I want to update my attribute to change its length. I am getting

Error: Permission denied for relation pg_attribute.

My SQL Query is like this:

DO $$
BEGIN
UPDATE
    pg_attribute
SET
    atttypmod = 5+4
WHERE
    attname LIKE '%my_attribute%'
    AND atttypmod = 1 + 4;
END $$;

I have set search_path to my user, my user has the usage on schema pg_catalog. Is this possible? Because if not, we would have to drop the idea of RDS DB and move to ec2-instance and creating our own postgres db

UPDATE:

Granting permission with this command:

GRANT UPDATE ON pg_attribute TO rds_superuser;

Result is:

WARNING:  no privileges were granted for "pg_attribute"
Query returned successfully with no result in 41 msec.

If you want to change the length of many varchar columns (which isn't actually a custom attribute), you are better off using ALTER TABLE commands. You don't risk having unwanted side-effects, just in case PostgreSQL needs to do something more than changing the pg_attributes table. It might not do it today, but might do it in any future version.

You can have the database help you generate the ALTER TABLE DDL statements (or even, generate and execute them within a UDF).

To generate the DDL statements, you can use the information_schema.columns view; which is simpler to understand than using pg_attributes together with more catalog tables all joined. [The view actually joins several tables in non-obvious ways, but that's already done for you.]

You first check the columns involved:

-- Check the columns you are interested in
SELECT
    table_schema, table_name, column_name,
    data_type, character_maximum_length
FROM
    information_schema.columns
WHERE
    column_name like '%varchar_column%'
    AND table_schema = 'my_schema' ;

You can get something like:

| table_schema | table_name |            column_name |         data_type | character_maximum_length |
|--------------|------------|------------------------|-------------------|--------------------------|
|    my_schema |         t1 |     one_varchar_column | character varying |                        5 |
|    my_schema |         t1 | another_varchar_column | character varying |                        6 |
|    my_schema |         t2 | a_third_varchar_column | character varying |                        3 |

Once you're sure the list of columns you get is the one you're actually interested, make the database compute the necessary DDL to perform what you want:

--
-- This will generate a collection of `ALTER TABLE` statements
--
-- This is what you're looking for
--
SELECT
    'ALTER TABLE "' || table_schema || '"."' || table_name || '" ' ||
    '    ALTER COLUMN "' || column_name || '" TYPE VARCHAR(9);' AS ddl_to_execute
FROM
    information_schema.columns
WHERE
    column_name like '%varchar_column%'
    -- AND table_schema = 'my_schema'
    -- AND whatever else you need
ORDER BY
    table_schema, table_name;

This will result in the following list of ALTER TABLE statements:

ALTER TABLE "my_schema"."t1" ALTER COLUMN "one_varchar_column" TYPE VARCHAR(9);
ALTER TABLE "my_schema"."t1" ALTER COLUMN "another_varchar_column" TYPE VARCHAR(9);
ALTER TABLE "my_schema"."t2" ALTER COLUMN "a_third_varchar_column" TYPE VARCHAR(9);

Copy them and execute them.

You can check the example at http://rextester.com/GYHR58531


Which MySQL directories to back up using FTP My server has been hacked and I only have FTP access to recover my data before reinstalling the OS.

Which/where are the directories that needs to be backed up? Will MySQL be recovered back to its initial state if I were to copy these directories back to the freshly installed server?

If you FTP /etc/my.cnf, read it and look for

datadir
innodb_data_home_dir : If this is not set, ignore this.

For most MySQL installations, this would be /var/lib/mysql


Many MYSQL processes on Linux Recently I deploy my parser on server and sometime server start reboot from out of memory, i spectate for memory and find something, exactly after every using parser, (one time on day) ram memory decreases, and after few day server has been crashes. Why MySQL after every stop parser create more trash in RAM?
Screen from htop sorted by use memory

Screen from my.cnf

If i reboot the system, amount of process doesn't change, and
systemctl status mysql present that mysql have 28 tasks.

If you use Innodb as format databases, you can set a buffer limit.
exemple for 4G ram :
[mysqld]
innodb_buffer_pool_size=3G
innodb_io_capacity=6000

source : https://dev.mysql.com/doc/refman/5.7/en/innodb-buffer-pool-resize.html


Is it possible to disable new version checking in pgAdmin 4? New versions of pgAdmin 4 are being released every month, and it's so annoying to see a popup with notification about another update. There's no option in preferences to disable it. Is there a trick to disable this particular popup, except installing the new version?

Add this line to config_local.py and restart the pgadmin4:

UPGRADE_CHECK_ENABLED=False

Note:
The location of config_local.py is same as config.py. You can find the location of config.py with this command in linux:

$ find / -wholename "*/web/config.py" 2>/dev/null

If the config_local.py file doesn't exist yet, you can create one.


Stored Procedure - Reseed Identity I have a bunch of LU tables that have values added and deleted all the time. Therefore, I figured writing a quick stored procedure would serve me well and then can call the stored procedure on another stored procedure that deletes values from the LU tables and then reseeds the ID column directly after.
Stored procedure code
Create Procedure DBA.ReseedIdentity

@TableName nvarchar(20) = NULL,
@IdentityColumn nvarchar(25) = NULL

AS BEGIN
SET NOCOUNT ON
SET XACT_ABORT ON

IF (@TableName is null or @IdentityColumn is null)
    BEGIN
        RAISERROR('You Must Specify a Table AND the ID Column from that Table to Reseed the Identity!', 16,1)
        RETURN;
    END

Declare @MaxID int
set @MaxID = (SELECT ISNULL(MAX(@IdentityColumn),0) FROM @TableName)
DBCC CHECKIDENT(@TableName, RESEED, @MaxID)

END

I wanted to know if there is any trick to be able to pass the @TableName parameter following the FROM clause without dynamically creating it. I am new to writing stored procedures, so please forgive me if the answer is obvious.
Any help is greatly appreciated.

If you are completely deleting all the values from the lookup table, have you considered just truncating them?  That would save on TLOG activity and automatically reseed the values.
You can't substitute parameters like that in queries, at least not for object names.  You would need to do this dynamically.
Please see an example for you below.  I beefed up the sanity check for you for edge cases you may not have considered... hopefully the below is clear.

CREATE PROCEDURE DBA.ReseedIdentity
(
    @TableName SYSNAME
    , @IdentityColumn SYSNAME
)
AS
BEGIN

/** Sanity Checking **/
IF @TableName IS NULL OR @IdentityColumn IS NULL
BEGIN
    ;THROW 50000, 'Must specify table name and Identity column name.', 1;
END

IF OBJECT_ID(@TableName) IS NULL
BEGIN
    ;THROW 50000, 'Tablename does not exist.', 1;
END

IF NOT EXISTS (SELECT TOP (1) 1 FROM sys.columns AS C WHERE C.name = @IdentityColumn AND C.object_id = OBJECT_ID(@TableName))
BEGIN
    ;THROW 50000, 'Identity column is not found on specified table.', 1;
END

/** Reset Identity
    Assumes Identity column is an INT
    **/
DECLARE @SQLCommand NVARCHAR(4000)

SET @SQLCommand = N'
    DECLARE @MaxID INT;
    SET @MaxID = COALESCE((SELECT MAX(' + QUOTENAME(@IdentityColumn) + ') FROM ' + QUOTENAME(@TableName) + '), 1);

    DBCC CHECKIDENT(' + QUOTENAME(@TableName, '''') + ', RESEED, @MaxID);
    ';

EXEC sp_executesql @SQLCommand;

END


what is the maximum capacity allowed for SQL managed Instance? I am looking in option to migrate to SQL Managed Instance but found on MSDN that it supports max of 8 TB in General tier and 4 TB Business critical. what are option for databases greater than 8 TB?
Is IAAS the only option available at this time? Is Microsoft planning to extend capacity limit on Managed Instance?

At this stage, IaaS is the only option for exceeding this size. Currently, Managed Instances carry the 4TB/8TB limit depending on your service tier and I don't believe there are any announcements about increasing this. Azure SQL Database can be sized up to 4TB, again depending on the service tier chosen.

There is a new tier for Azure SQL Database in preview called Hyperscale (https://docs.microsoft.com/en-us/azure/sql-database/sql-database-service-tier-hyperscale) which may provide a solution, but this is not generally available yet. This only appears to apply for Azure SQL Database, and not Managed Instances according to the documentation available so wouldn't be an exact answer for the Managed Instance limits.


showing locks from all postgres relations I'm trying to show all locks (DBMS-wide) in Postgres.

I initially tried adapting the documented example to:

SELECT pg_database.datname AS database, relation::regclass, pg_locks.database AS db_oid, pg_locks.relation AS rel_oid,
        locktype, page, tuple, virtualxid, transactionid, classid, objid, objsubid, virtualtransaction, pid, mode, granted
    FROM pg_locks
    LEFT OUTER JOIN pg_database ON (pg_locks.database = pg_database.oid)
    ORDER BY database, relation;

but Postgres seems to only be able to resolve the names of relations in the current catalog:

    database    |                relation                 | db_oid  | rel_oid |   locktype    | page | tuple | virtualxid  | transactionid | classid | objid | objsubid | virtualtransaction |  pid  |           mode           | granted
----------------+-----------------------------------------+---------+---------+---------------+------+-------+-------------+---------------+---------+-------+----------+--------------------+-------+--------------------------+---------
 franknfurter   | pg_locks                                | 2881378 |   11000 | relation      |      |       |             |               |         |       |          | 40/1709521         |  9684 | AccessShareLock          | t
 janet          |                                         | 3820378 | 3820698 | relation      |      |       |             |               |         |       |          | 97/3461972         | 19937 | AccessShareLock          | t
 janet          |                                         | 3820378 | 3820698 | relation      |      |       |             |               |         |       |          | 97/3461972         | 19937 | RowExclusiveLock         | t
                | pg_database_datname_index               |       0 |    2671 | relation      |      |       |             |               |         |       |          | 40/1709521         |  9684 | AccessShareLock          | t
 brad           |                                         |  772666 |  772946 | relation      |      |       |             |               |         |       |          | 28/1711346         |  9317 | ShareUpdateExclusiveLock | t
 franknfurter   | idx_posts_user_id_deleted_at            | 2881378 | 2883250 | relation      |      |       |             |               |         |       |          | 21/2032902         |  9602 | RowExclusiveLock         | t
 franknfurter   | pg_class                                | 2881378 |    1259 | relation      |      |       |             |               |         |       |          | 40/1709521         |  9684 | AccessShareLock          | t
 franknfurter   | posts_pkey                              | 2881378 | 2881707 | relation      |      |       |             |               |         |       |          | 21/2032902         |  9602 | RowExclusiveLock         | t
                |                                         |         |         | virtualxid    |      |       | 97/3461972  |               |         |       |          | 97/3461972         | 19937 | ExclusiveLock            | t

is this just a limitation of postgres or am I doing something differently?

You can only resolve names from the current database. This is because the pg_class table, which is what the ::reglass operator uses to resolve the names, is local to each database.


Why can't I call my UDF directly? Why do I need to qualify it 3 levels deep? I just created a UDF and testing it I found only works when I use this syntax

SELECT [PMIS].[dbo].[fnIsReportingTo] (50,1132)

I was expecting it to work like this:

select * from fnIsReportingTo (50,1132)

or

select fnIsReportingTo (50,1132)

here is my create SQL:

create function fnIsReportingTo
(
@BossID int,
@EmployeeID int)
RETURNs bit
AS
begin
declare @ret bit
    if exists (select * from dbo.fnGetEmployeeHierarchy(@BossID) where employeeID=@employeeID)
        select @ret=1
    else
        select @ret=0
return @ret
end

Scalar UDFS must be qualified with schema. You don't need the database part of the qualified name unless it's in a different database of course.

SELECT [dbo].[fnIsReportingTo] (50,1132)

In the FROM clause, you can only use table valued functions

See "Types of Functions" in MSDN

Edit: As an observation, I'd tend to avoid:

nesting UDFs this
using table lookups in scalar UDFs

if you use this function for each row of another table you have a cursor that iterates over the table in the UDF for every outer row

Even this is better. It's now table valued and expands like a view.

create function fnIsReportingTo (
    @BossID int,
    @EmployeeID int)
RETURNs table
AS
return (
      select CAST(COUNT(*) AS bit) AS FooBar
      from dbo.fnGetEmployeeHierarchy(@BossID)
     where employeeID=@employeeID)
GO


TempDB on Shared VHDX Multiple nodes within a cluster. Using HyperV as the virtualisation layer. VMs use Fibre Channel SAN for storage but trying to explore the possibility of using Local SSDs for the TempDB. Issue is when the VM migrates.

I know TempDB doesn't need to persist after migration as the DB will just recreate it, but, when it migrates, the VHD will no longer exist. I would imagine the DB would fail if it can't find the directory to create the TempDB?

Is it possible to create a VHDX per node, which sits on the exact same directory (For example, it is always the 'T' Drive), so that every VM will simultaneously write to it regardless of which node they are on? I don't want a VM on Node 2 trying to write to a local drive on Node 1. I want to keep things local at all times.

As far as SQL Server config: Since you're not using SQL Server clustering with shared storage, I believe you don't need to do anything but configure all your instances with Tempdb on T drive (or whatever standard path you choose). See Example A at https://msdn.microsoft.com/en-gb/library/ms345408(v=sql.110).aspx.

As for how to ensure that T drive is available as local storage in all your VMs for when SQL Server starts up, that someone else will have to help with (perhaps stackoverflow with virtualisation tags).

And be sure to test your whole failover process, don't take anyone's word for it.


What is not persisted in a database backup / restore? (SQL Server 2005) If I take a backup of a database on a SQL 2005 Server, and then restore that backup to a different (new) database on the same database server instance, what things will not be persisted (things like cached execution plans, statistics etc...)

Plans will be recreated at first run.

Otherwise, everything database level will be there except where it interacts at the instance level, such as:

replication
backup jobs/schedules
maintenance plans/schedules
login mappings to users

For a standard, everyday database, you've no worries.


"Request to run job dbxxxxx refused" error in merge replication I'm doing merge replication whole of my database  but when I  create the subscription I encounter this error:

SQLServerAgent Error: Request to run job DBXX-XX--bazresiostani2-2 (from User sa) refused because the job is already running from a request by User sa.
Changed database context to 'DBXXX'. (Microsoft SQL Server, Error: 22022)

but I don't have this error when creating replication with some tables in database.
The publication database is 2008R2 and the subscriber database is 2005

Try restarting SQL Server Agent service.

(per here)


Filter results from distinct count I need to modify a procedure to make statistics on my database.

I got a temporary table which contains all the data that I need to make those statistics.
It contains all the relevant actions FormIdNb of the working units NumCavalier in one day :

-- Every action from all units will be stored in this temp table
-- DateHeure = Time of action
-- NumCavalier = ID of working unit
-- FormIdNb = Action type
CREATE TABLE #tmp_DailyMissions (
DateHeure datetime,
NumCavalier  nchar(3),
FormIdNb int
)

INSERT INTO #tmp_DailyMissions
SELECT        DateHeure, Cavalier.NumCavalier, FormIdNb
FROM            ECN4, Cavalier
WHERE        (DateHeure BETWEEN @StartTime AND @EndTime) AND (Cavalier.Terminal = @terminal) AND (Cavalier.NumCavalier = ECN4.NumCavalier)

This temporary table will store all actions that all the working units made in one entire day (just as a logging system).
The significant values for FormIdNb are :

  FormIdNb > 3 ==> The working unit was active
  FormIdNb = 3 ==> The working unit was idle
  FormIdNb < 3 ==> The working unit was inactive

The request that I got right now (Thanks to @AndriyM), returns the number of working units who were ACTIVE, IDLE and INACTIVE on each interval of time of the day (15min) :

WITH time_cte(StartTime, EndTime) AS
  (
    SELECT
      StartTime = @starttime,
      EndTime   = DATEADD(mi, 15, @starttime)

    UNION ALL

    SELECT
      EndTime,
      DATEADD(mi, 15, EndTime)
    FROM
      time_cte
    WHERE
      EndTime < @EndTime
  )
SELECT
  t.StartTime,
  ActiveUnitCount   = COUNT(DISTINCT(CASE WHEN d.FormIdNb > 3 THEN d.NumCavalier END)),
  IdleUnitCount     = COUNT(DISTINCT(CASE WHEN d.FormIdNb = 3 THEN d.NumCavalier END)),
  InactiveUnitCount = COUNT(DISTINCT(CASE WHEN d.FormIdNb < 3 THEN d.NumCavalier END))
FROM
  time_cte AS t
  LEFT OUTER JOIN #tmp_DailyMissions AS d
    ON d.DateHeure >= t.StartTime
    AND d.DateHeure < t.EndTime
GROUP BY
  t.StartTime
;

The problem is, as I said above, that the temporary table I use store the data as a logging system.
This means that it can contain, for a working unit on a same interval of time, any value of FormIdNb.
For example : 1 row FormIdNb = 2, 1 row FormIdNb = 3 and 1 row FormIdNb = 4.

My question is :
How to make a request who get rid of irrelevant values ?

More precisely :
If a working unit was ACTIVE, but was also IDLE and/or INACTIVE on an interval of time, how to "remove" this working unit from the IDLE and INACTIVE counts, and only keep the maximum value of FormIdNb for each intervals of time ?

Perhaps, I'm missing something, but it sounds like to want the max(formidnb) for each NumCavalier in the time slice you're processing.  I haven't fully syntax checked this, but I think you could add the following to just above your semicolon

AND d.FormIdNb =
(
select MAX(FormIdNb)
FROM #tmp_DailyMissions
WHERE NumCavalier = d.NumCavalier and
DateHeure >= t.StartTime
AND DateHeure < t.EndTime
)


Increase memory limit when server is stopped There was a local SQL Server 2012 instance. Once memory has decreased to critical 128 MB and then has stopped by me.

I can not start it (idea: not enough memory).

Is it possible to increase this limit in config file or in system registry?

Open SQL Server Configuration Manager
In the SQL Server Services pane, locate the instance that has the problem
Right-click that instance and select Properties
On the Startup Parameters tab, type -f, click Add, then click Apply
Right-click that instance and select Start (or Restart)

This will start the instance in minimal configuration mode (single user, no CHECKPOINT, and no startup procedures are run), which should allow you enough resources to connect and fix what you broke.


MySQL Add/Grant user if it does not exist I am trying to create a update/install script that adds a user and database only if it's a fresh install. The script uses a randomly generated password for the user, so using the GRANT statement as suggested in a StackOverflow thread will not work.

Is there a way to create, set password, and grant privs a user only if it does not exist?
Am I going to have to run a check in the script and then run the statement or can I build it into a SQL file?

What you need is Dynamic SQL

SET @sql_found='SELECT 1 INTO @x';
SET @sql_fresh='GRANT ALL ON *.* TO randomuser@''%'' IDENTIFIED BY ''randompassword''';
SELECT COUNT(1) INTO @found_count FROM mysql.user WHERE user='randomuser' AND host='%';
SET @sql=IF(@found_count=1,@sql_found,@sql_fresh);
PREPARE s FROM @sql;
EXECUTE s;
DEALLOCATE PREPARE s;

If @found_count is 1, then SELECT 1 INTO @x; will execute. (this query does nothing)

Otherwise, GRANT ALL ON *.* TO randomuser@'%' IDENTIFIED BY 'randompassword';

Give it a Try !!!


Creatin Products Database with Multiple Color Variations I found this Q&A already, that describes my problem and a more or less satisfying answer:
Create products with color variants
Basically, I want to set up a database for products with different color variations. Here the solution is given of setting up a product-table, a color-table, and a product-color-table as a combination of both.

Unfortunately, most of my products are available in ~180 different colors and we have about 8000 different articles. Additionally, every product has at least one standard color (up to ~5 standard colors) out of the 180 colors in which the product is cheaper than in other colors.

Do I really have to set up a database as described before, which would lead to over 1 Mil articles, or is there another way?
I thought of setting up a product-color-table in the sense, that there are 180 columns for each color and I just assign them values like "available, not-available, standard" or so. And then all the other information that don't really affect the price of the products in yet another table.

But since I'm new to setting up databases I don't really how I can realise it. Would that actually work? Can anyone give me any tips on how I can solve that and set up my tables?

Thanks in advance

PS: In case my description was confusing here is an example of an onlineshop I found that has the exact thing I want to do:
When you tap on "Ciew colour range" you can see all the different colors. But then there are also other articles that are available in less colors or different standard-colors.

Firstly I would advise strongly against having a column for each colour - This will cause very slow queries and will scale poorly (There is also a data size limit on the ammount of data in a single row in Microsoft SQL Server so this could cause you problems if your number of colours grows).

To represent the many-to-many relationship you have described I would use an intermediary table like this:

CREATE TABLE [Product]
(
ID INT IDENTITY(1,1) PRIMARY KEY
,Name NVARCHAR(250)
)

CREATE TABLE [Colour]
(
ID INT IDENTITY(1,1) PRIMARY KEY
,Colour NVARCHAR(250)
)

CREATE TABLE [Product_Colour_Availability] -- Many to Many relationship table
(
ID INT IDENTITY(1,1) PRIMARY KEY
,Product_ID INT
,Colour_ID INT
,Available bit -- 1=available, 0=not available
)

--
-- Insert some data for a single product (T-Shirt) that will be both red and blue.

INSERT INTO [Product] (Name)
VALUES ('T-Shirt')

INSERT INTO [Colour] (Colour)
VALUES ('Red'), ('Blue')

INSERT INTO [Product_Colour_Availability] ([Product_ID], [Colour_ID])
VALUES (1,1,1), (1,2,1)

-- Get availability info about a specific product:

SELECT P.[ID] AS 'Product ID'
,P.[Name] AS 'Product Name'
,C.[Colour] AS 'Product Colour'
,PCA.[Available] AS 'Availability'
FROM [Product_Colour_Availability] PCA
LEFT JOIN [Product] P ON PCA.Product_ID=P.[ID]
LEFT JOIN [Colour] C ON PCA.Colour_ID=C.[ID]
WHERE P.[ID] = 1


How to import an existing schema and tablespace into a postgres database, from a filesystem-level backup Follow-up question from:
Have postgres' pg_dump export an index

Say I have an existing database, and I want to add to it another schema with content. I saved that content previously through a filesystem-level backup of the tablespace that served the schema's tables and indexes.

I understand that DATABASEs, TABLEs and INDEXes necesitate a tablespace to be stored into, but not SCHEMAs (their declaration is stored in the DATABASE's tablespace?).

So, how do I make the DATABASEs tablespace understand that it needs to access a specific schema that would have its tables and indexes defined through a pre-existing tablespace.

Alternatively, can I make a CREATE TABLE t TABLESPACE tspace statement that would connect to the tspace TABLESPACE (as opposed to starting to write into it an empty instance of the table)?

(In other words, is the execution of a DDL statement stored into the DATABASEs tablespace, and nothing touches another tablespace?)

Say I have an existing database, and I want to add to it another
  schema with content. I saved that content previously through a
  filesystem-level backup of the tablespace that served the schema's
  tables and index

Unfortunately, an isolated backup of a tablespace cannot be used for any purpose whatsoever.

I think what you're trying to do is based on the  premise that a tablespace could be more or less moved around, be possibly plugged to a different database or the same database at a different point in time, that kind of thing. But none of this is possible. A tablespace holds live metadata in rows and elsewhere that become wrong the moment you isolate it from the rest of the instance.

The results of DDL statements go into the pg_catalog schema of each database. pg_catalog also contains references to objects that are shared across all databases and not stored in any particular database, such as pg_authid or pg_database.

Looking at Have postgres' pg_dump export an index I can see where your idea is coming from. However I don't see how @mustaccio answer has any practical use. If you have a FS-level image of a table plus its indexes, they having matching TIDs, and then what? There's no practical use to these files outside of the rest of the cluster.


Oracle DBID what is logic to create oracle DBID? Oracle DBID what is logic to create oracle DBID ? Does it ensures uniqueness across multiple database in different host.?

Can I relay on DBID to identify database among 100 database in the organization ?

Thanks,

DBID is a numeric identifier that utilities like RMAN use to identify databases.

You can get your DBID like this:

select DBID from v$database;

However it NOT unique especially if you have copies of your database where you hot start them with:

recover database until change <SCN> using backup controlfile;

but if you don't have (control file) copies, then it is unique.

Because it's a number it's not a nice way to uniquely identify databases.

What we do to uniquely identify our databases across the whole company, is to create TNS entries in the form of "SID.host" because SID is unique on the host, and then use this format in our global tnsnames.ora.


How we can do full text search and facets stuffs on BigQuery? In the future, we will have millions ofs record in our web product. So we had used BigQuery for data storage and analysis. We have to build filters on our search page like Flipkart and Amazon provide product filters.

Basically, we need different filters on left side of our search page. With help of these filters, we can see our desired results. Every filters have their count, i.e., record counts in that category/term. So we have to build queries in such way that they give result and count of every category(term). Here, "category" means different types filter on different columns as we see on e-commerce sites, e.g., Flipkart, Amazon etc.

  Faceted search (also called faceted navigation, guided navigation, or parametric search) breaks up search results into multiple categories, typically showing counts for each, and allows the user to "drill down" or further restrict their search results based on those facets.

Is there any framework/plugin available like Solr which can be used with BigQuery to provide above desired functionality?

Is BigQuery not suitable for above purpose? Do we need to stick with any RDBMS database (e.g., PostgreSQL, MySQL, etc.) and search engine (e.g., Solr, Elasticsearch, etc.) for this purpose?

Is BigQuery not suitable for above purpose? Do we need to stick with any RDBMS database (e.g., PostgreSQL, MySQL, etc.) and search engine (e.g., Solr, Elasticsearch, etc.) for this purpose?

Correct, BigQuery does not provide search or faceting capabilities. BigQuery is a data warehouse, so it's good for running analytics over large amounts of data, but it does not provide indexing, search, or facets.

You may be able to use Google Cloud Datastore (document database) for this, since it provides secondary indexes, or you may want to do as you suggested, and use a combination of a SQL database such as MySQL or PostgreSQL together with a search product such as Elasticsearch or Solr.


SSRS report stuck on Loading but ExecutionLog says otherwise I have a report that is stuck on loading in the reporting server portal (it's been over a day). However when i check the ExecutionLog...

select * from ExecutionLog3
WHERE UserName = 'SOMEREPORTSERVER\SomeName'
AND ExecutionID = 'cbk0hdnpzrspg5qpkvqxubyn'
order by TimeStart desc

...it says that the status is 'rsSuccess'.

Here is the AdditionalInfo XML which might help... The query took about 1 minute to run which is how long it took to run in SSMS. Rendering seems pretty fair with the amount of data returned so I am not sure what's going on.

I made sure that Interactive Sizing is standard 8.5 x 11 so that it can show at least some data while processing the other pages but that did not help.

<AdditionalInfo>
  <ProcessingEngine>2</ProcessingEngine>
  <ScalabilityTime>
    <Pagination>0</Pagination>
    <Processing>0</Processing>
  </ScalabilityTime>
  <EstimatedMemoryUsageKB>
    <Pagination>263471</Pagination>
    <Processing>3795406</Processing>
  </EstimatedMemoryUsageKB>
  <DataExtension>
    <SQL>2</SQL>
  </DataExtension>
  <Connections>
    <Connection>
      <ConnectionOpenTime>24</ConnectionOpenTime>
      <DataSets>
        <DataSet>
          <Name>nocontacts</Name>
          <RowsRead>79650</RowsRead>
          <TotalTimeDataRetrieval>10297</TotalTimeDataRetrieval>
          <QueryPrepareAndExecutionTime>0</QueryPrepareAndExecutionTime>
          <ExecuteReaderTime>10225</ExecuteReaderTime>
          <DataReaderMappingTime>0</DataReaderMappingTime>
          <DisposeDataReaderTime>0</DisposeDataReaderTime>
        </DataSet>
      </DataSets>
    </Connection>
    <Connection>
      <ConnectionOpenTime>30</ConnectionOpenTime>
      <DataSets>
        <DataSet>
          <Name>contacts</Name>
          <RowsRead>724715</RowsRead>
          <TotalTimeDataRetrieval>70893</TotalTimeDataRetrieval>
          <QueryPrepareAndExecutionTime>0</QueryPrepareAndExecutionTime>
          <ExecuteReaderTime>26506</ExecuteReaderTime>
          <DataReaderMappingTime>0</DataReaderMappingTime>
          <DisposeDataReaderTime>0</DisposeDataReaderTime>
        </DataSet>
      </DataSets>
    </Connection>
  </Connections>
</AdditionalInfo>

Edit: SSRS - 14.0.807.134 / SSMS - 14.0.17177.0 / Browser - Chrome
Edit: Trying IE - 11.309.16299.0 - I don't have the access to update report viewer settings.

Sometimes the browser will time out or otherwise not return a rendered report even though the process is still running in the background and ultimately succeeds.  The browser will likely not even tell you it failed, it will just keep spinning or return a blank page.  The logs are indicating "success" meaning the process itself did indeed run correctly, but the logs won't tell you if browser successfully returned a rendered report.  I've usually seen this happen around the 10 minute mark, regardless of the configuration of server or report level timeout settings.  Your Execution Log shows it as over 10 minutes, so this is the possible reason.  Also, the Execution Log returned a massive Byte Count (~353 MB), and this may also be a culprit.

There are a few possible solutions to this problem:

Limit the amount of data that will be rendered in browser using pagination.  The Data Retrieval & Processing time should stay the same, but the Rendering time will be reduced as it is only returning the first page, and will only render other pages when you navigate to those using the arrows.  Reflecting back on the aforementioned massive Byte Count, so somewhere along the way it may have broke the camel's back.
Once the data is processed, exporting to a static format, such as Excel or PDF, will also likely be successful & should throw an error if it failed, rather than it attempting to render the entire report in browser as HTML.
Deploy the report in the export format to a File Share using a Subscription.  This will allow the process to run in the background on the server and render it to a file directly.  If you are unable to schedule this on a regular interval, this may be a bit inconvenient for the user to request it from an admin and require that admin to manually deploy it.  However, I believe this is better than the report user rerunning the same report multiple times, resulting in concurrent executions, further compounding processing issues, affecting all users.


Will an AD Account inherit all the User Mappings to databases that it's parent AD Group is mapped to? If a Windows AD Group and an AD Account that belongs to that AD Group are both setup as Logins on a SQL server, will the AD Account Login inherit all the User Mappings that it's parent AD Group Login are currently mapped to?

Or would I need to re-apply the same User Mappings to the AD Account Login that are already setup on the parent AD Group Login?

Yes, a Windows account will have all of the permissions that are assigned directly to the login as well as any permissions that are assigned to any groups the account is a member of.

Permissions are cumulative, but DENYs will override.  It would be difficult or impossible to accommodate some security needs if they were not.

See Effective SQL Server permissions when user is in several AD groups.  The same principals apply to permissions assigned to a user and to a group they are a member of.

In general, DBAs that have been around a while will advise you to never start giving permissions to individual user accounts, as you can then end up with a long list of logins, and when employees leave you have to go clean them up.  The preferred way is to only assign permissions to groups, and then when an employee leaves, or is hired, the DBA doesn't have to do anything as the request for access is granted by whoever manages the AD groups.


Storing a json string that is 10 kb in size in a SQL Server CE cell I have a SQL Server CE database in which I have column that takes values of json strings. These json strings are above 9kb as a result of which I am unable to use varchar or char. I guess I could tackle this through the usage of ntext.

But in SQL Server, this is marked to be removed in an upcoming edition. I am not sure of SQL Server CE. Also I am having where and having conditions / statements on other values stored in this column.

Therefore having ntext may not help as well. Are there any other data types that I could use to store this json string? Any help would be much appreciated.

Just because something is marked as deprecated doesn't meant that it's disappearing soon - there are features that have been marked deprecated for several versions which haven't been removed yet. That said, you're right to move away from deprecated features if the opportunity presents itself (but don't make changes simply for the sake of change), and you should definitely avoid implementing anything new using a deprecated feature.

Per Microsoft's documentation on ntext, the replacement for ntext is nvarchar(max).

You may also be interested in reading about SQL Server 2016's support for JSON data.


You don't need to find the ids with no matches when you finally filter for a percentage > 0.

Standard SQL based, but fully untested:

with cte as
 (
   select Id, Type, Value,
      count(*) over (partition by Id) as RowCount -- maximum number of possible matches
   from tab
 )
select t.ID, cte.Id as RelatedID,
   count(*) as countMatches,
   max(cte.RowCount) as Compared,
   100.0 * count(*) / max(cte.RowCount) as PercetageMatched
from tab as t -- or cte if you need the RowCount, too
join cte
  on t.Type  = cte.Type
 and t.Value = cte.Value
where t.id < cte.id
group by t.ID, cte.Id

I don't know about the actuallogic, but you might do the percentage calculation based on the min (or max) of both RowCounts.

Edit:

Of course this will create a huge intermediate result set, mainly based on the count of duplicate values. Following SQL calculates the number of rows before aggregation:

select sum(cnt * (cnt-1) / 2), max(cnt), count(*)
from
 ( select count(*) as cnt
   from tab
   group by type, value
 ) as dt

You might materialize the result of the cte in a table with a clustered index on (type, value, id), maybe this increases performance...


How to generate replication snapshot without locking tables When I reinitialize subscription and generate new snapshot it locks all tables and makes the database unusable for a very long time (never waited it out).

Is there anyway to do this without locking?

The whole point of snapshot is to provide a point in time copy of the articles involved in replication. It does it when generating the snapshot files from the publisher by locking the tables until the snapshot is finished for that particular article.

You should plan to reinitialize during less activity time of your server.

  Is there anyway to do this without locking?

In SQL Server 2005, Concurrent snapshot is by default (applies to only PUSH subscriptions)

sync_method = concurrent

What it does is that it produces native-mode bulk copy program output of all tables but does not lock tables during the snapshot.

  Not if the new snapshot should include schema changes. –  Kenny Johansen Sep 16 '14

Also, as per your comment, if you are doing schema changes (e.g. adding new columns) and want to replicate those across to subscribers, you can use this method.


guessing unused junk space in table. (innodb mysql/mariadb ) We have in every site (a dozen) one big database with lots of tables and record, using innodb as default and with innodb_file_per_table. from time to time we have space issue on some site and I am able to reclaim space from big table with a ALTER TABLE mydb.mytable ENGINE=InnoDB.

Hopefully that works, but I don't know table will return useful unused space a with table will not. I mean some big table have sometimes NO useless space, and some have a lot, depending how much DELETE have been done (I guess).
Is there a way to know in advance how much space is allowed for deleted record so I can run ALTER TABLE mydb.mytable ENGINE=InnoDB only on table with lot of useless space.

After digging the question, I went with a SELECT like this one:

SELECT table_name from information_schema.tables WHERE table_schema = 'mydb' AND DATA_FREE > 0 ORDER by DATA_FREE DESC;

So I have a list of table to process.

a scripted form would be :

#! /bin/bash
[ -z "$1" ] && echo usage "$0 [database_name]"
[ -z "$1" ] && exit 1
for T in $(mysql -ss -e "SELECT table_name from information_schema.tables WHERE table_schema = \"$1\" AND DATA_FREE > 0 ORDER by DATA_FREE DESC;");do echo \"$T\";time mysql -e "SET sql_log_bin=0;alter table $1.$T ENGINE=InnoDB";done 2>&1


Remote MySQL connection fails (10060) - "Can't connect to MySQL server on domainname.com(10060)" I have developed an application in VB6, and am using an online MySQL database server for storing my application's data.

The first time I try to connect to my database server is successful, but after closing my application and starting it again I get:

  Can't connect to MySQL server(10060)"

After some time (approximately 30 minutes), I can successfully connect to the database again.

I have given maximum values to all of MySQL system and global variables like connection_timeout, wait_timeout etc.
I have granted all permissions to my MySQL user.
The firewall on my computer is turned off.
All connections are properly closed when the application closes.

I have asked the technical people who provide the Online MySQL Database service and they gave me these three reasons:

The local machine from where you are trying to connect is not whitelisted on the server.
The maximum number of connections for the user has been exceeded.
The MySQL server was down at the time (though they said that their server was not down).

How can I solve the first two reasons, or determine that they are the cause of the problem? Or could there be another problem not listed there?

you need to make sure there is:

a user created that has remote access for example user@'x.x.x.x'

the two machines can establish a connection (they can ping each
other for example or access each another by another method) the port
is opened between the two machines

check your configuration file  my.ini/my.cnf for parameters like bind-address or skip-networking. Is there a blocking connection parameter
like:bind-address=a.a.a.a or skip-networking?

the service is up and running when you try to connect from the
other machine


When is running a graph database like OrientDB in cluster mode required? OrientDB can run in a distributed environment, supports ACID transactions and peer to peer replicaiton.

According to the book "NoSQL Distilled: A Brief Guide to the Emerging World of Polyglot Persistence" by M. Fowler and P. Sadalage, graph databases work best in a single server configuration (chapter 4.1).

What is the reason for this statement and when should I use a clustered version for a graph database?

You ask two different questions here:

  [W]hen should I use a clustered version for a graph database?

This one is easy: as soon as your scalability or availability requirements cannot be met by One Giant Server.

  [G]raph databases work best in a single server configuration[...] What is the reason for this statement?

Looking at the full book quote you can see a hint at the reason (emphasis mine):

  Although a lot of NoSQL databases are designed around the idea of running on a cluster, it can make sense to use NoSQL with a single-server distribution model if the data model of the NoSQL store is more suited to the application. Graph databases are the obvious category here—these work best in a single-server configuration.

As the book to which you are referring explains, there are two non-mutually-exclusive approaches to distributed databases: sharding (partitioning) and replication.

Sharding works well when all entities in a shard are tightly coupled together and simultaneously loosely, or not at all, coupled with entities in other shards. This is rarely the case with a graph model, where each node can potentially be linked to all other nodes1.

Replication works well when updates to your data are small in scope (e.g. a small number of entity instances per unit of work). Again, in a graph model a change of one node can potentially affect the entire graph, or a large portion of it, which in an ACID-compliant database causes almost exponential growth of the transaction volume, since you must update at least the majority of replicas before the transaction can be considered durable.

Things become even more complex with peer-to-peer, that is bidirectional, replication, where you have to deal with conflict resolution on top of that.

As a result, distributing a graph database (where a true graph model is justified) does not provide much benefit from the scalability perspective. Partitioning a graph database doesn't improve availability either.

1 - If it is not, then you need multiple graph databases, or even a relational database instead.


Problem getting SQL Server 2016 R Services (In-Database) working SQL Server 2016 CU2 (Enterprise) here and one of my users has requested that we get R-Services installed.

I followed what appeared to be very simple instructions from this page

Set up SQL Server R Services (In-Database)

When I got to the part about using the simple test

exec sp_execute_external_script  @language =N'R',
@script=N'OutputDataSet<-InputDataSet',
@input_data_1 =N'select 1 as hello'
with result sets (([hello] int not null));
go

it failed with

  Msg 39021, Level 16, State 1, Line 6 Unable to launch runtime for 'R'
  script. Please check the configuration of the 'R' runtime. Msg 39019,
  Level 16, State 1, Line 6 An external script error occurred:  Unable
  to launch the runtime. ErrorCode 0x80070057: 87(The parameter is
  incorrect.). Msg 11536, Level 16, State 1, Line 6 EXECUTE statement
  failed because its WITH RESULT SETS clause specified 1 result set(s),
  but the statement only sent 0 result set(s) at run time.

So, I started my internet searching for known problems regarding R-Services and found this article - Known Issues for SQL Server R Services

I made sure that R-Services had been brought up to date with the Sql Server version (2016, CU2).
I read where long file names in the rlauncher.config file might cause problems, so I redirected my WORKING_DIRECTORY to C:\TEMP\R_SERVICES.  However, I left the RHOME and MPI_HOME configuration entries untouched and still using long file names.
I made sure the Windows group SQLRUserGroup had full authority to that working directory folder.

After every configuration change, I restarted both SQL Server and SQL Server Launchpad.

However, every time I try to run the sample T-SQL test for R-Services, it fails and I see this entry in the rlauncher.log file

  [Error] Session creation failed with: Failed with 2 to get security
  for C:\TEMP\R_SERV~1\MSSQLSERVER01

I'm reaching out for help from others that might know what the problem is or any additional troubleshooting links.

We wound up having to copy two folders (MSSQLSERVER01 and MSSQLSERVER02) from our Sql Server install directory, which for us was located here

E:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\ExtensibilityData

We copied the above folders to the new WORKING_DIRECTORY (C:\TEMP\R_SERVICES) we had specified in the rlauncher.config file.

After that, we were able to run the sample R_Services testing query

exec sp_execute_external_script  @language =N'R',
@script=N'OutputDataSet<-InputDataSet',
@input_data_1 =N'select 1 as hello'
with result sets (([hello] int not null));
go


mysql error handling in function I am trying to do error handling as I can do in stored procedures in mysql but functions don't support to support them. Is there a way I can do something like this?

delimiter //

CREATE FUNCTION supports_ft()
    RETURNS INT
    BEGIN
    DECLARE CONTINUE HANDLER FOR SQLSTATE 'HY000' return 0;
    SELECT @@innodb_ft_cache_size;
    return 1;
END;

Functions support exception handlers, but that won't necessarily catch a blatantly invalid operation that could never succeed, such as what you are trying here, which is an unbounded select.  This is only supported in stored procedures, not functions (or triggers).

ERROR 1415 (0A000): Not allowed to return a result set from a function

You need to try to load the system variable's content into a dummy variable.  The following should work, unless MySQL tries to validate the variable at function declaration time, which it may or may not... you'll need to verify:

SET @dummy = @@innodb_ft_cache_size;

If validation is done early, you'll need to use a prepared statement to avoid that early validation.

HY000 is a pretty vague sqlstate, and you might be better off with DECLARE CONTINUE HANDLER FOR SQLEXCEPTION.


How do you reset the master key in sql server 2005? Our old DB admin is gone, any ideas?

It looks it was set up many years ago.

Assuming that you are trying to reset the database master key you just need to use the ALTER MASTER KEY statement adding in the keyword FORCE.

ALTER MASTER KEY
FORCE REGENERATE WITH ENCRYPTION BY PASSWORD = 'YourNewPassword!';


Group by but preserve statistical probability of value in limit Consider a table in which a specific column is not unique:

name
--------
john
paul
paul
george
ringo
paul
paul
paul
paul

How might one return N users by random, but preserve the statistical likelyhood that the user with the most entries is most likely to be returned? This would be similar to actually drawing N names by way of slips of paper from a hat, with the stipulation that if I draw a slip with the same name as one already drawn that I would discard it and draw another.

If I use GROUP BY and LIMIT, then MySQL first removes the dupes, then performs the limit. Note also that there are many fields returned, so using DISTINCT on the field list is not an option.

Of course, I would prefer to do this is a single query and not loop over a query. Note also that the database might contain less than N entries, in which case obviously each unique user will be returned.

That was a challenge.  I think this will do the job:

CREATE TEMPORARY TABLE t (
     name VARCHAR(99)  NOT NULL,   -- this datatype should match your `name`
     rnd DOUBLE  NOT NULL
)
    SELECT name, RAND() FROM your_table;

SELECT name, MIN(rnd) AS x
    FROM t
    GROUP BY name
    ORDER BY x
    LIMIT ... ;    -- Fill in the "N" you want


MySQL: Process was running fast on DigitalOcean after moving it to my local machine it runs slower I have a Symfony project in which I crawl rss feeds and collect articles. I used to have it hosted on Digital Ocean with a droplet instance of 4 cores and 8GB RAM.
In the biggest table I have around 500k rows, on Digital Ocean the MySQL instance was running pretty ok handling the crawl processes (between 11 and 20 parallel crawl processes).
Due to the fact that the disk space was filled on my droplet (it reached 44GB on my biggest table) I decided to take another approach and move the whole project on my local machine.

DO Instance:

4 vCPUs
8GB RAM
50GB DiskSpace (don't think it's important)
Ubuntu Linux Server 18.04

Local machine:

8 Core CPU (Intel Core i7 7700)
32 GB RAM
2TB HDD (mysql data folder is held on this hard disk)
128GB SSD - os is installed on this
Ubuntu Linux Desktop 18.04

Is there a difference between MySQL versions installed on desktop and server ubuntu versions?
My DO instance CPU usage (htop) was almost always approaching 100% on all 4 CPUs, on my local machine the CPU usage is around 20% on each core while running 20 crawl processes at a time.
I might be missing a certain configuration on my local machine?
Running a simple UPDATE query on an InnoDB table that is not used by the project but is in the same database takes forever (I've been waiting for 10 minutes now almost). Running a SELECT (over through ssh tunneling) on the same table runs smoothly.
All tables in my project are InnoDB.
Having a look at mytop I have around 20 SELECT queries and 4 update queries right now.

The DO instance was running both a webserver and the mysql instance and it handled everything well from my point of view. On my local machine at this point I'm using the webserver only on my internal network.

Can't figure it out, can anyone give me a hint?

On the DO instance I didn't do any special settings to MySQL and neither to my local machine other than changing the connection_timeout to 24hours + 1 minute (I needed it in another project where I had a process sleep for 24 hours and needed the connection still available when resuming the process).

mysql -V returns:

mysql  Ver 14.14 Distrib 5.7.24, for Linux (x86_64) using  EditLine wrapper

Check the configs on the two machines (my.cnf or wherever)
100% CPU almost always means a missing index and/or a poorly formulated query.
Turn on the slowlog if not already on.  (And have long_query_time=1)
DO probably had SSD drives; you have HDD.  But other clues indicate that I/O is not the problem.
innodb_buffer_pool_size should be about 26G on your 32GB machine; did you set that?
Multiple crawlers implies lots of INSERTs?  Do you batch the inserts or do it one row at a time?
See this for how to provide me data to investigate the slowlog and the tunables further.


MySQL Subquery hangs / terrible perfomance I am using MySQL 5.5.16 @ CentOS.

Please have a look at this query:

SELECT
    inventory_ID,
    (
    SELECT
        price_brutto
    FROM
        _inventory_prices
    WHERE
        _inventory_prices.inventory_ID = _inventory.inventory_ID AND
        price_type_ID = 4
    ORDER BY
        price_date DESC
    LIMIT 1
    ) as price_brutto
FROM
    _inventory
ORDER BY
    inventory_ID ASC
LIMIT 1

It takes forever to execute this query! But separate queries:

SELECT
    inventory_ID
FROM
    _inventory
ORDER BY
    inventory_ID ASC
LIMIT 1

AND

    SELECT
        price_brutto
    FROM
        _inventory_prices
    WHERE
        _inventory_prices.inventory_ID = 12345 /* this is first inventory_ID from _inventory table */ AND
        price_type_ID = 4

Runs in < 0.01s ! (First query returns 1 row, second 0 rows)

_inventory table is filled with ~60k rows
_inventory_prices table is filled with ~40m rows

Why main query (with subquery) is so terribly slow?

Structure:

CREATE  TABLE IF NOT EXISTS `ca_ERP`.`_inventory` (
  `inventory_ID` INT(11) NOT NULL AUTO_INCREMENT ,
  `SKU` VARCHAR(200) NULL DEFAULT NULL ,
  `item_ID` INT(11) NOT NULL ,
  ... data columns ...,
  PRIMARY KEY (`inventory_ID`) ,
  UNIQUE INDEX `SKU_UNIQUE` (`SKU` ASC) ,
  INDEX `fk__inventory__items1_idx` (`item_ID` ASC) ,
  CONSTRAINT `fk__inventory__items1`
    FOREIGN KEY (`item_ID` )
    REFERENCES `ca_ERP`.`_items` (`item_ID` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
AUTO_INCREMENT = 64930
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_general_ci

CREATE  TABLE IF NOT EXISTS `ca_ERP`.`_inventory_prices` (
  `inventory_prices_ID` INT(11) NOT NULL AUTO_INCREMENT ,
  `inventory_ID` INT(11) NOT NULL ,
  `price_type_ID` INT(11) NOT NULL ,
  `price_brutto` DECIMAL(9,2) NULL DEFAULT '0.00' ,
  `currency_ID` INT(11) NULL DEFAULT '1' ,
  `is_custom` TINYINT(4) NULL DEFAULT '0' ,
  `price_date` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ,
  PRIMARY KEY (`inventory_prices_ID`) ,
  INDEX `fk__inventory_prices__inventory1_idx` (`inventory_ID` ASC) ,
  INDEX `fk__inventory_prices__currencies1_idx` (`currency_ID` ASC) ,
  INDEX `fk__inventory_prices__marketplaces1_idx` (`price_type_ID` ASC) ,
  INDEX `dateindex` (`price_date` ASC) ,
  INDEX `priceindex` (`price_brutto` ASC) ,
  CONSTRAINT `fk__inventory_prices__currencies1`
    FOREIGN KEY (`currency_ID` )
    REFERENCES `ca_ERP`.`_currencies` (`currency_ID` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk__inventory_prices__inventory1`
    FOREIGN KEY (`inventory_ID` )
    REFERENCES `ca_ERP`.`_inventory` (`inventory_ID` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk__inventory_prices__marketplaces1`
    FOREIGN KEY (`price_type_ID` )
    REFERENCES `ca_ERP`.`_price_type` (`price_type_ID` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
AUTO_INCREMENT = 3408033
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_general_ci

Explain result:

1   PRIMARY _inventory  index       PRIMARY 4       1   6220900.00  Using index
2   DEPENDENT SUBQUERY  _inventory_prices   ref fk__inventory_prices__inventory1,fk__inventory_prices__marketplaces1    fk__inventory_prices__inventory1    4   ca_ERP._inventory.inventory_ID  177 100.00  Using where; Using filesort

The query is slow because the dateindex is used and that is not the best index for this query (or no index at all, please fix the EXPLAIN output). When the subquery is executed, the inventory_ID is already known and the price_type_ID is fixed but there is no composite index that can make this information useful.

Possible explanations for the single query running faster is that you either get cached results from previous execution of the query or it uses different index (and by luck this is a bit more efficient than the dateindex). You have several indexes, on (price_type_ID), on (inventory_ID) but no composite one. So, depending on statistics, the actual parameters and the index chosen each time, the performance will vary.

Try adding an index that will always do an index seek, no index or table scan (and get the price from the index, too, so no additional read form the table will be needed) and thus make both the query and the single query much faster:

ALTER TABLE _inventory_prices
  ADD INDEX price_type_ID__inventory_ID__date__brutto__IX        -- choose a name
    (price_type_ID, inventory_ID, price_date, price_brutto) ;


Cube processing either takes too long or fails I have a SSAS Cube with 35 dimensions and 10 measures.

Some of the dimensions are quite large in size.
In almost all dimensions data either get updated and inserted.
Measures have high volume of data.

When I run from SSMS (by right click on SSAS Database) "process full" on database it takes around 1 Hr 30 min.

When I am Processing cube (process full) from SQL Agent job via XMLA script it takes around 1 Hr 20 min.

When I processing dimensions and measures (Process Full) separately via SSMS it is taking 1 hr 50 min.

But when I am processing dimensions and measures from SSIS (DML Task via XMLA) it is taking more than 5 hours. [Memory consumption went to almost 100%]

So my questions are:

Does processing dimensions and measure from SSIS can cost that much?
What kind of processing should I choose for fast cube processing. I am doing Process full.
When I am processing cube from first two options then most of the time cube is getting failed by saying "attribute key not found" but it works well when I process dimensions and measure separately. Doesn't process full cube ensure proper processing of dimension and measures?

Oh my dear..

First of all. In your case I would like to suggest you to choose the "Process Default" option. If you are using Process Full always you are dropping all data and objects and recreating all of your stuff. You don't need them. Very often the proposal option is to use ProcessData followed by ProcessIndex.

I didn't proces the cube from within the SSIS, but probably SSIS is checking something and locking something on the database. The best option is to use XMLA. If you want to process your cube from within the SSIS you can write a script task to perform the processing - you can find a scripts on the net.

You need to also check if you have a clean data. Check twice in your project and in your data if you have all the keys and so on. If you are processing separately dimension the data is not comparing between measures and dimensions. I am pretty sure that you have some keys in fact tables that have no corresponding values in your dimension. Make an order with this. If you have no values in dimension and it's correct (doesn't exist in the source tables) you can add to each dimension one extra value with i.e. key "-1" and all values like "N/A" (not applicable) and point the not founded values to this record in the dimensions.

And at the end. If you have a big SSAS database please think about a partitioning.

It's a lot to talk about. If you have some specific questions feel free to ask.


SQL Server Multi Tenancy Setup What is the best way to use Multi Tenant Database Architecture when all of the "Tenants" will be from the same company?

We are going to be using AWS and SQL Server for our backend system. We currently have 1000+ facilities that will end up using this new system and are steadily increasing by roughly 10-20% a year on acquiring new facilities that will have to be brought onto the system.

The debate we are currently having is if we should have a single massive database that handles/stores all of the transaction's for all of our facilities, or if we should scale out to a 1 database per facility architecture.

The biggest requirement we have is the ability for Global Accounts. A customer needs to be able to have a rental item with us in a Facility in Florida as well as a Facility in Washington.

The idea of how to set this up we have is this:

Have 1 Master Database that holds all of "global" data for the company. Meaning things like Accounts or config settings that are the same at every facility.
Have 1 SQL Server Instance per time zone we have facilities in (for less latency across the geo locations) and then have 1 database per facility in that time zone on the instance.
All of the individual Facility Databases would have the exact same schema/table structure.
We would also want to set up some sort of Streaming replication with ETL from all of the Databases into a Data Wharehouse that would ideally be located on AWS as well.

Has this type of setup been done before? Is this even considered a good approach?

Thanks!

What you are describing seems exactly like PartitionDB*.  You can create your multi-tenant database design in more than one mode. Look at their configuration page.

Basically, you have a master database which populates metadata structure information between the database nodes. They support SQL Server and also a builtin replication mechanism if you like to use.

* I am a beta user, but not affiliated with this product otherwise.


Assigned filetype for backups not applying I am working to migrate backups over to a new storage point. I figured the easiest thing to do was to duplicate the configuration over from the existing Maintenance Plan to a new one, just in case something goes wrong with the new backup point in the short term.

I copied over the existing configuration and ran the Maintenance Plan to test, but ran into an issue where one of the DBs didn't append the .bak filetype. Instead, it went with nothing or .b as the filetype.

The code within the T-SQL on the new backup Maintenance Plan matches the original, so I am unsure why it's acting like this.

After a bit more investigation and review, we found that the filename size was too long and was cutting off the tail end of the name. It's very likely the issue has been going on for some time, but was never noticed until the changeover.


MySQL store procedure use select responce in INSERT...SELECT where in array I am pretty sure that this is a simple matter and that it's answered but I seems like not able to find it online, or to phrase it correctly.

I have a table (ven_data) that has a few columns: id_v, lang, more data columns
The unique constraint here is (id_v, lang) pair.

I need to find all ID's that have some languages missing, and then INSERT...SELECT from one of the languages to all the missing languages.

What I do now, I am fetching ALL ID's that has less then the number of languages I need:

SELECT id_v, COUNT( 1 ) as cnt
FROM  `ven_data`
GROUP BY id_v
HAVING cnt <8
ORDER BY cnt ASC

Then I export it to Notepadd++ and remove all unneeded data, make a line of ID's like this: 1,4,5,6

And then make the INSERT...SELECT

INSERT INTO `ven_data`(`id_v`, `lang`, data columns)
SELECT `id_v`, elementInner, data columns
FROM `ven_data` WHERE `id_v` in (1,4,5,6) and `lang` = element
        on duplicate key update lang=elementInner;

Now I have not problem iterating over the languages in the stored procedure but how do I get the result of the first SQL into the id_v in (1,4,5,6) ???

I'll choose to make the database generate a (virtual) table with all (id_v, lang) pairs that you are expected to have at the end.
To do so, you start by getting the list (actually set) of all DISTINCT lang:
SELECT DISTINCT
    lang
FROM
    ven_data ;

... and let's imagine it is this one

| lang |
| :--- |
| EN   |
| FR   |
| IT   |
| CA   |
| DE   |
| DK   |
| GR   |
| NL   |

We do the same to get the set of all DISTINCT id_v:
SELECT DISTINCT
    id_v
FROM
    ven_data ;

| id_v |
| ---: |
|    1 |
|    2 |
|    3 |
|    4 |
|    8 |
|    9 |
|   11 |
|   12 |
|   13 |

What should be in your ven_data table is actually the cartesian product of both sets. That is accomplished in SQL by means of a CROSS JOIN.
That results in:

id_v | lang
---: | :---
   1 | EN
   2 | EN
   3 | EN
   4 | EN
   8 | EN
   9 | EN
  11 | EN
  12 | EN
  13 | EN
   [...]
   1 | NL
   2 | NL
   3 | NL
   4 | NL
   8 | NL
   9 | NL
  11 | NL
  12 | NL
  13 | NL

What you actually want to do is INSERT in your table all the rows in this cartesian product that aren't already there.
You do this by means of:
INSERT INTO
    ven_data
    (id_v, lang)
SELECT
    id_v, lang
FROM
    -- The whole set of (id_v, lang) pairs
    (SELECT DISTINCT
        lang
    FROM
        ven_data
    ) AS languages
    CROSS JOIN
    (SELECT DISTINCT
        id_v
    FROM
        ven_data
    ) AS id_list
WHERE
    -- The pair (id_v, lang) is not already in place
    NOT EXISTS
    (SELECT
        1
    FROM
        ven_data v2
    WHERE
        v2.id_v = id_list.id_v AND v2.lang = languages.lang
    ) ;

Note that this WHERE condition can also be written in more than one fashion (in MySQL):
WHERE
    (id_v, lang) NOT IN
    (SELECT
        id_v, lang
    FROM
        ven_data v2
    ) ;

You can see the whole setup (together with all the steps 1 by 1) and you can experiment at
dbfiddle here

NOTEs: you don't need to do this from a stored procedure, nor you need to use arrays. This code is quite standard SQL. Note that it also works with PostgreSQL  or SQL Server, for instance.


Find if Stored Procedure Error Is there a method in T-SQL to see if error occurred in Stored Procedure, without the use of @Output or try catch?

We have a lot of legacy code 200+ stored procedures. I have seen @Output method and try catch, however we would have to refactor legacy code, which may break. Just curious, if there is a way to catch error without changing old code.

declare @TestErrorFlag bit -- or can be int or varchar(255)

-- trying to do something similar to this
set @TestErrorFlag  = if error(exec dbo.testprocedure)

Trying to store in some type of indicator variable

@TestErrorFlag = 1 if procedure succeeds,
@TestErrorFlag = 0 if procedure fails

If an error condition in your stored procedure raises an exception, your only option without changing the code inside the procedure, really, is to wrap the call in try/catch:

BEGIN TRY;
  EXEC sys.sp_helpdb @foo = 1;
END TRY
BEGIN CATCH
  PRINT ERROR_MESSAGE();
END CATCH;

If your procedure returns an error number without bubbling up an exception, e.g.:

CREATE PROCEDURE dbo.bob
  @fail bit = 0
AS
BEGIN
  IF (@fail = 1)
  BEGIN
    RETURN -1;
  END
  -- unnecessary, but:
  ELSE
  BEGIN
    RETURN 0;
  END
END

Then you can use this format:

DECLARE @TestErrorFlag int;
EXEC @TestErrorFlag= dbo.bob @fail = 1;
PRINT @TestErrorFlag;

Of course in this case if any error condition in the procedure does lead to an exception, the PRINT won't happen.

Error handling is a massive topic. I would browse around these articles:

Error and Transaction Handling in SQL Server - Part 1
Error and Transaction Handling in SQL Server - Part 2
Error and Transaction Handling in SQL Server - Part 3


SQL Server : how can I measure database performance increase after index maintenance? We are using a SQL Server 2005 database.

We currently have no maintenance on our databases indexes. Some of our indexes are showing over 85% fragmentation when looking at the table sys.dm_db_index_physical_stats.

We plan to reorganise the indexes on our own server as a test before performing the same operation on a client site. On our own server any negative impact will have minimal or no effect on our business. After this we will set up an automated plan to reorganise the indexes.

But before being able to roll this out to our client sites (2005 and 2008r2) we will have to justify the action and to do this we have to show non-technical people a measure of improvement. How can I justify my existence? How do I measure/demonstrate any improvements?

You could use the RML utilities to capture a workload trace before and after the index rebuild and compare execution times.

RML utilities come with a reporter application that can compare the workload analysis of two trace files.

You can download it from here:

X64: http://download.microsoft.com/download/0/a/4/0a41538e-2d57-40ff-ae85-ec4459f7cdaa/RMLSetup_AMD64.msi

X86: http://download.microsoft.com/download/4/6/a/46a3217e-f523-4cc6-96e9-df73dd0fdd04/RMLSetup_X86.msi

The comparison will be more meaningful if the two workloads are comparable, even better if identical.