texts: Why is my Redo Log Apply service "spotty"?

texts:
I have a 12c Dataguard configuration between apple (primary) and orange (physical standby). The following is my entry in ldap:

apple=
(DESCRIPTION_LIST=
 (DESCRIPTION=
  (ADDRESS_LIST=
   (ADDRESS=(PROTOCOL=TCP)(HOST=apple_home)(PORT=15940))
   (ADDRESS=(PROTOCOL=TCP)(HOST=orange_home)(PORT=15940)))
    (CONNECT_DATA=(SERVICE_NAME=virtual_apple)(FAILOVER_MODE=(TYPE=SESSION)(METHOD=basic)))
 )
  (DESCRIPTION=
   (ADDRESS=(PROTOCOL=TCP)(HOST=apple_home)(PORT=15940))
    (CONNECT_DATA=(SERVICE_NAME=apple)(FAILOVER_MODE=(TYPE=SESSION)(METHOD=basic)))
 )
)

My goal with this is to allow clients to sqlplus user@apple regardless of which database is the current primary. This is why you see virtual_apple in the connect string, it is a service created that only runs on current primary. I added the second DESCRIPTION so that in the event of a switchover where apple is the standby, Broker can connect from orange to apple (see orange parameter below). It will otherwise fail to connect because it will be looking for the virtual_apple service on the standby which is undesirable due to the fact that users would then be redirected to the standby when running sqlplus user@apple.

The problem that I have been encountering is that redo shipping and applying seems "spotty". One minute there will no problems and the next I will get ORA-16857: standby disconnected from redo source for longer than specified threshold and redo logs stop applying. I then set TransportDisconnectedThreshold='120' as I have found in articles. However, the problem persists.

I suspect this is due to my network service name description. Perhaps Broker takes too long trying to reach the wrong service name which throws ORA-16857 but then hits the right one but then loses it again. Is my connection string wrong/problematic? Is there a parameter I could add to help solve this issue? Is my issue elsewhere?

On orange:

log_archive_dest_2='service="apple"','ASYNC NOAFFIRM delay=0 optional compression=disable max_failure=0 max_connections=1 reopen=300 db_unique_name="apple" net_timeout=30','valid_for=(online_logfile,all_roles)'


texts: What is the Difference between IN and Not IN performance wise?

texts:
I am new to database.I am having doubt that what is the difference between IN and NOT IN performance wise.

When I am using IN, takes less time for the same query(Logically same query) when I use NOT IN.


texts: MySQL (mariaDB) trigger syntax always wrong

texts:
I'm making a small game and I need to have a trigger on my members table, which will check the experience column, and anytime it gets bigger than certain values, the level will become level+1. This is the trigger:

DELIMITER $$
CREATE TRIGGER update_level AFTER UPDATE ON members
FOR EACH ROW BEGIN
    IF experience < 1000 THEN
        SET members.level = 1
    ELSE IF experience < 2400 THEN
        SET level = 2
    ELSE IF (experience < 4000) THEN
        SET level=3
    ELSE IF (experience < 8000) THEN
        SET level=4
    ELSE IF (experience < 15000) THEN
        SET level=5
    ELSE IF (experience < 24000) THEN
        SET level=6
    ELSE IF (experience < 35000) THEN
        SET level=7
    ELSE IF (experience < 50000) THEN
        SET level=8
    END IF
END
$$
DELIMITER ;

phpMyAdmin always responds with this error code:

  #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use

I spent about 2 hours trying to figure it out, but it looks like I can't do it by myself. What I have tried is to change the IF statement to a CASE statement (simple switch). I have tried it with WHEN xxx THEN SET. Also with UPDATE. Another thing  I have tried was to add ' ' and " " everywhere around the column names and values - didn't help at all.

Can you please advise me about what's the best way to accomplish this? I'm pretty sure there is some small mistake there, and if not that, then my approach must be wrong.


texts: How to sort records before inter-row calculation?

texts:
I have a time sequence data set to analyze but the difficulty is that

the data set is very big.
the time sequence events are committed by different objects with affiliation.
its in mysql, there is no id like index column to join

e.g.

----------------
dt     obj        comp
----------------
t1,    object_a,   component_1
t2,    object_b,   component_1
t3,    object_b,   component_2
t4,    object_a,   component_1
t5,    object_b,   component_1
t6,    object_b,   component_2
----------------

I am trying to know the delay between each commit
from each component of each object.
i.e.
obj_a.comp_1.delay1 = obj_a.compoent_1.t4 - obj_a.component_1.t1

etc.

I tried to join

select timediff( t1.dt, t.dt ) as delay
from  table as t
join table as t1
on t1.comp = t.comp
and t1.obj = t.obj
and t1.dt = (
    select min(t2.dt)
    from table as t2
    where t2.obj = t.obj
    and t2.comp = t.comp
    and t2.dt > t.dt
)

which taking forever, so I am thinking if there is any way to sort on the results and do inter-row calculation with variables, it would be much faster.
but it failed every way with sub-query and views where the sort is always done after calculation. From what I read on order by, it seems impossible. So what options do I have to to achieve this reasonably fast?

To be more specific:
If I do the following:

SELECT
    obj
    , comp
    , dt - @prev
    , @prev := dt
FROM
   table
ORDER BY obj, comp, dt ASC

the result is

obja, comp1, t1-null
obja, comp1, t4-t3      # should be t4-t1
objb, comp1, t2-t1
objb, comp1, t5-t4      # should be t5-t2
objb, comp2, t3-t2
objb, comp2, t6-t5      # should be t6-t3

Since the calculation happens on unsorted data, the results do not make sense. The table in my case is actually a view, the component is calculated field. It doesn't have an index. The join-subquery way could takes half an hour.
But if I save the sorted results into a table and calculate with variable, it only takes minutes.
The data set is very big, I only tested with a small subset.
I am not sure the method of saving to extra table is a scalable solution.
So I am looking for advise on alternatives.


texts: Compare words in a string without considering their positions?

texts:
In Postgres 9.6 I want to test whether two strings like these are considered the same:

'this is a test number 01', 'number this is 01 a test'

So I have created this function:

CREATE OR REPLACE FUNCTION sort_text(a text) RETURNS text AS $$
    declare t1 text;
    BEGIN
      select(array_to_string (
        array(
          select * from unnest(string_to_array(a, ' ')) order by 1), ' ')) into t1;
      RETURN t1;
    END;

$$ LANGUAGE plpgsql;

select (sort_text('this is a test number 01') = sort_text('number this is 01 a test'));

which actually looks to be working correctly.

I was wondering, is there any better way to do this?

'this and this' and 'and this' are considered to be different.

All the strings are already stripped out (spaces and punctuation) and duplication is not a problem. String length 50 characters as max estimation.


texts: Does Oracle Instant Client 12c support LDAP?

texts: I've been using Oracle Instant Client 12c as a lighter-weight alternative to the full Oracle installation. It works fine when I resolve service names with tnsnames.ora, but now I would like to configure it to use LDAP. I dropped our corporate ldap.ora and sqlnet.ora files into the TNS_ADMIN directory, but they aren't being used when I try to connect to a database. So I'm wondering, is LDAP even supported by the Oracle Instant Client?


texts: Microsoft TDE Permissions For Changing Keys

texts:
I am setting up roles as a layer of protection inside of MSSQL. One of the roles will only have the ability to change database encryption keys for TDE on the database server. The server will be using EKM to supply the keys.

I have not been able to find the exact process to exchange the keys with EKM. Since I can't find the process, I can't give the roles the permissions it needs.

Right now, I have granted CONTROL permissions on certificates and asymmetric keys at the database level. I am assuming that this will be necessary.

Does anyone know if there are any server level permissions required to change the database encryption key? I believe that the user will at least need to have access to which keys are located in EKM.


texts: Compare last data and second last data sql server based on id

texts:
How do I create a view to find the last 2 records for a company id and to display the last value along with the difference between the last and the second last?

Here is some example data:

CREATE TABLE #161689 (
Dt DATE NULL,
Value INTEGER NULL,
CompanyId INTEGER NULL
);

INSERT INTO #161689
VALUES
('12/01/2010', 10, 10),
('11/01/2010', 5, 10),
('10/01/2010', 2, 10),
('08/10/2010', 1, 10),
('12/01/2010', 7, 20),
('10/01/2010', 3, 20),
('09/01/2010', 2, 20),
('08/10/2010', 2, 20);

Here is the expected result:

Date - Value - CompanyId - Diff

12.1.2010 - 10 - 10 - 5

12.1.2010 - 7 - 20 - 4


texts: Oracle strong ref cursor with dynamic sql

texts: I'm using Oracle 10g. I have a query in a stored procedure that selects entire rows from a table. Currently it returns a strongly typed ref cursor (tablename%rowtype). Now I have to add some dynamic WHERE clauses to this query, but dynamic SQL doesn't support a strong ref cursor. It has been asked on SO before. My question is: is there a workaround? Use some kind of wrapper or conversion or DBMS magic to convert from the weak cursor to the strong cursor? I can guarantee that the results match the table rowtype.


texts: SQL Developer: choose the tablespace where a new table will be stored

texts: When creating a new table, is it possible to specify the tablespace where the table is going to be stored?


texts: How do you verify that all InnoDB tables are in good shape?

texts: Without using a script, Is there a way to get mysql to check all the tables at once to make sure they're not corrupted?


texts: Create one-to-many to two fields in different table

texts:
I have a database where I am trying to figure out how to join a table that contains an object called ModelType.  The calibration table contains two fields (EffModel and BkgModel) that are based on a ModelType.  I cannot figure out how to join these two fields. Below is an image of the stucture.  I am trying to make sure that if I were ever to update one of the model types, that I would not have to update the reference Calibration.

I thought about creating a table called EffModelType and BkgModelType, which would separate the two fields depending on the same table but cannot for the life of me think of what would go in it.

Updated Info:
I may have misspoken about EFFModel and BkgModel Being based on Model type.  Essentially an example table model type is as follows:

ModelTypeID  | ModelName    |   ModelNumber
+++++++++++++++++++++++++++++++++++++++++++
     1       |  Linear      |       1
     2       |  Exponential |       2

and the EFFModel will be assigned a model type such as 1 (linear) and the BkgModel will also be assigned a model type, such as 2 (Exponential). Also the ModelNumber is an identifier from another application that will eventually be deleted so it can be ignored.


texts: PostgreSQL replication with fsync disabled?

texts:
I need to run PostgreSQL in-memory (for performance reasons), so I intend to disable fsync, which means that no writes will be sent to the WAL.

However, as part of my scheme to meet another requirement (that the in-memory database have somewhere to recover from when volatile memory is lost), I would like to stream or otherwise push writes to a replica. However, the PostgreSQL hot standby capability is based on the WAL. Clearly, I can't use this.

How could I achieve these goals using PostgreSQL features?

Thanks.


texts: Oracle streams how exclude chages in sequences in schema replication

texts:
I have two DBs I want to synchronize using streams

So I followed N-way replication guide.
I have following config:

   BEGIN
      DBMS_STREAMS_ADM.ADD_SCHEMA_RULES(
        schema_name    => 'schema_A',
        streams_type   => 'capture',
        streams_name   => 'capture_A',
        queue_name     => 'strmadmin.captured_DBA',
        include_dml    => TRUE,
        include_ddl    => TRUE,
        inclusion_rule => TRUE);
    END;
    /
    BEGIN
      DBMS_STREAMS_ADM.ADD_SCHEMA_RULES(
        schema_name     => 'schema_A',
        streams_type    => 'apply',
        streams_name    => 'apply_from_DBB',
        queue_name      => 'strmadmin.from_DBB',
        include_dml     => TRUE,
        include_ddl     => TRUE,
        source_database => 'DB_B',
        inclusion_rule  => TRUE);
    END;

I also want to utilize the sequence hint so I don't have duplicate keys:

create sequence SEQ1 ... start with 1 increment by 2...; --on DB A
create sequence SEQ1 ... start with 2 increment by 2...; --on DB B

Bu since I have schema replication if I run those two create sequence commands I have SEQ1 start with 2 increment by 2 on both DBs

Is it possible to exclude changes on sequences in schema replication or I need to change to table replication and specify all tables one by one?


texts:
We had a production emergency yesterday: out of the blue, in the middle of peak usage time, our SQL Server just stopped working.  Never mind no new connections; even existing connections stopped functioning.  I restarted the service and everything came right immediately, but in the interim we lost about 30 minutes of downtime and got serious egg on our faces.

There are no errors or warnings in the event log.  The only clue that something was going wrong is a series of "Information" messages in the event log from the time the outage began, every 5 minutes, saying:

  New queries assigned to process on Node 0 have not been picked up by a worker thread in the last 1500 seconds. Blocking or long-running queries can contribute to this condition, and may degrade client response time. Use the "max worker threads" configuration option to increase number of allowable threads, or optimize current running queries. SQL Process Utilization: 0%%. System Idle: 98%%.

So it seems like we're running out of worker threads, of which we have 576.  And I have no idea how that can happen.  I know that there are no particularly long running queries, because I am constantly running a Trace to pick up anything longer than 2 seconds, and we're getting no more than one or two of those every 5 minutes.

Also, we have not made any significant changes to the system for the last several weeks, and this kind of thing has never happened before.  Perhaps our usage has been growing, but if it was a resource overload, I would have expected the CPU to be maxed out, rather than at the beach around 0-2%.

What could have happened?  How do we diagnose, and how do we prevent this happening again?

Microsoft SQL Server 2014 (SP2) (KB3171021) - 12.0.5000.0 (X64)
    Jun 17 2016 19:14:09
    Copyright (c) Microsoft Corporation
    Standard Edition (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor)


texts: Enabling LOAD DATA LOCAL INFILE in mysql

texts:
LOAD DATA LOCAL INFILE is not enabled by default. Normally, it should be enabled by placing local-infile=1 in my.cnf. But it does not work for all installations. In my experience, it worked for Debian 7, but not Debian 7 minimal, though both installations come from the same precompiled deb package. Both on OpenVZ VPS.

How to debug why local-infile=1 does not work for an installation, and how to safely activate LOAD DATA LOCAL INFILE?


texts: Why is SSMS showing red squigglies(sp?) even though the table exists?

texts:
I'm creating a new table and in the process creating two foreign keys. The tables with the primary keys referenced exist but Intellisense is giving the error message: "Foreign key. . .references invalid table 'ROLES'."

Why does SQL Server Management Studio not recognize these tables?


texts: How to convert bit(n) to bytea when n >= 256?

texts:
How can I convert a BIT(256) into 64-byte BYTEA? More generally, how can I convert from BIT(n) to BYTEA when n is large enough that I can't cast the bits through an INT8?
Example: I've got a bunch of sha256 hashes stored in a table:
CREATE TABLE foo(sha256 CHARACTER(64) NOT NULL); -- hex encoded sha256 digests
INSERT INTO foo (sha256) VALUES
   ('e643a277fab67cca43d9348623c7551d7382784033a697eb7f5484178be7c53d')
 , ('73483f797a83373fca1b968c11785b98c4fc4803cdc7d3210811ca8b075d6d76')
 , ('b2b91baab6785f6bc6bc43c3a72f700b6bc64c478b744c539c80a857f55b00ca')
 , ('ba8cf9b1f2827f46d45b5061a65a11e93b849174185af9d92508a38b4df04945')
 , ('7507a8f88da14e9bdd5acc310ad260943bc788249a49de210205c1b537f41406')
 , ('868ca27b41d804c4877275595b4a2cf5d85559d6805693f95b68a09a69dc8634')
 , ('f10480d4b52a000b8fd8643147396b869f7c11727623023525b4ca041242356e')
 , ('53ce065eaa1e2f10eb86d10d07f8c748ce482bea9897018862f91cd142bb1eb9')
 , ('4e5d320a6c34bea34f1746bf8a85d00cf77d6d8a3b52d8649c93b2282d3f64e6')
 , ('9f1b957647c30fdd05ad319092226143e437965014235343113dc8dd6a58dda7');

CREATE TABLE bar AS
  SELECT ('x' || sha256)::BIT(256) AS sha256
  FROM foo;

/* Doesn't work:
SELECT sha256::BYTEA FROM bar; -- cannot cast type bit to bytea
*/

Notes:
I got the ('x' || str)::BIT(n) trick from https://stackoverflow.com/questions/17208945/whats-the-easiest-way-to-represent-a-bytea-as-a-single-integer-in-postgresql/17214093#17214093


texts:
I have two SCHEMA in a database(ABC,XYZ)

Now i want to create a user Test and grant select privilege only on SCHEMA (XYZ)

I know how to grant Privilege to a user, but confused how to grant privilege on specific SCHEMA?


texts: SQL Server - Anyone use SUMA, trace flag 8048, or trace flag 8015?

texts:
Recently included SQL Server startup Trace Flag 8048 to resolve a serious spinlock contention issue in a SQL Server 2008 R2 system.

Interested to hear from others who have found usage cases where performance value was delivered by trace flag 8048 (promote query memory grant strategy from per-NUMA node to per-core), trace flag 8015 (SQL Server ignores physical NUMA), or SUMA (interleaved sufficiently uniform memory access, a BIOS option on some NUMA machines).

Trace flag 8048
http://blogs.msdn.com/b/psssql/archive/2011/09/01/sql-server-2008-2008-r2-on-newer-machines-with-more-than-8-cpus-presented-per-numa-node-may-need-trace-flag-8048.aspx

Trace flag 8015
http://blogs.msdn.com/b/psssql/archive/2010/04/02/how-it-works-soft-numa-i-o-completion-thread-lazy-writer-workers-and-memory-nodes.aspx

Gory details of system workload, gathered metrics from troubled system, and gathered metrics from the system after intervention follow.

Trace flag 8048 was a 'fix', but was it the best fix? Would SQL Server ignoring physical NUMA due to trace flag 8015 have accomplished the same thing? What about setting the BIOS to interleave memory, leaving the server with SMP-imitating SUMA behavior instead of NUMA behavior?

Peace!
tw: @sql_handle

About the system:
 - 4 hex core Xeon E7540 @ 2.00GHz, hyperthreaded
 - 128 GB RAM
 - WS2008R2
 - MSSQL 2008 R2 SP2
 - maxdop 6

About the workload:
 - 1000s of Batch scheduled/queued reports driven from 2 report application servers.
 - 3 flavors of batches: daily, weekly, monthly
 - All report application servers connections to SQL Server are made as a single service account
 - Maximum report concurrency = 90

Key findings on the troubled system:
- From Perfmon, 15 second intervals
- - System remains at 95%-100% CPU busy
- - SQL Server buffer page lookups < 10000 per/second

From wait and spinlock DMVs, 5 minute intervals

High CMEMTHREAD waiters and wait time

High SOS_SUSPEND_QUEUE spins and backoffs

Bob Dorr's CSS Engineer Blog post on trace flag 8048 indicates that systems with more than 8 cores per NUMA node can run into similar symptoms due to bottleneck in query memory grant. Trace flag 8048 will change the strategy to per-core instead of per-NUMA node.

The Intervention

MSSQL was restarted with -T8048 in place. The difference was immediately evident: buffer page lookup rate rose over 1 million and spike to 8 million per second. The troubled batch workload, which previously couldn't complete in 24 hours, completed in less than 4 hours. Another batch workload which was not the focus of investigation or intervention was submitted as part of validating the corrective value of trace flag 8048 (and ensuring that its unwanted side effects were minimal). This report batch previously completed in 2 hours; with trace flag 8048 in place the report batch completed in roughly 20 minutes.

Nightly ETL also encountered a benefit. ETL time dropped from roughly 60 minutes to 40 minutes.

Pulling together information from several places, I speculate that the high degree of report queuing, the concurrent report count greater than hardware thread count, and the single user account for all reports combined to put pressure on one NUMA node until worker thread pressure caused it to be disfavored for the next incoming connection request for the same user account, at which point the next NUMA node would get some number of connections near instantly. Each NUMA node would end up with a high probability of stressing the query memory grant bottleneck.

Opening more lanes for query memory grant removed the bottleneck. But, I'm not sure the cost. Bob Dorr's CSS post makes it clear that there is additional memory overhead with trace flag 8048. Is that overhead within the single-page allocator region governed by MSSQL 2008 R2 max server memory? If so, I guess the system will just have some number fewer database pages in the buffer pool cache. If not, should max server memory be lowered to accommodate?


texts: How to correctly implement optimistic locking in MySQL

texts:
How does one correctly implement optimistic locking in MySQL?

Our team has deduced that we must do #4 below or else there is a risk that another thread can update the same version of the record, but we'd like to validate that this is the best way to do it.

Create a version field on the table you want to use optimistic locking for e.g. column name = "version"
On selects, make sure to include the version column and make note of the version
On a subsequent update to the record, the update statement should issue "where version = X" where X is the version we received in #2 and set the version field during that update statement to X + 1
Perform a SELECT FOR UPDATE on the record we are going to update so that we serialize who can make changes to the record we are trying to update.

To clarify, we are trying to prevent two threads which select the same record in the same time window where they grab the same version of the record from overwriting each others if they were to try and update the record at the same time. We believe that unless we do #4, there is a chance, that if both threads enter their respective transactions at the same time (but have not issued their updates yet), when they go to update, the second thread that will use the UPDATE ... where version = X will be operating on old data.

Are we correct in thinking we must do this pessimistic locking when updating even though we are using version fields/optimistic locking?


texts: VIM returns "Error detected while processing" and "Sorry, the command is not available in this version"

texts:
When I run VIM with \e inside of psql, I get

Error detected while processing /home/ecarroll/.vimrc:
line   11:
E319: Sorry, the command is not available in this version: call plug#begin('~/.vim/plugged')

However I don't get this error outside of PSQL. Further visual block mode in VIM doesn't work when I try to replace.


texts: Multiserver administration - passing maintenance plans to clustered instances

texts:
SQL Server 2019 MSX/TSX environment. TSX servers are clustered instances. MSX is not. There is a SQL integration service on each of the nodes in the cluster. I create a maintenance plan on the MSX and when i try to push it to the TSX i get this error:

Message
[000] Failed to import Maintenance Plan 'MaintenancePlanMSA' from MSX: Microsoft.SqlServer.Management.Sdk.Sfc.EnumeratorException: Failed to retrieve data for this request. ---> Microsoft.SqlServer.Management.Common.ExecutionFailureException: An exception occurred while executing a Transact-SQL statement or batch. ---> System.Data.SqlClient.SqlException: Parameter 0 is incorrect for this statement.
The statement has been terminated.
at Microsoft.SqlServer.Management.Common.ConnectionManager.ExecuteTSql(ExecuteTSqlAction action, Object execObject, DataSet fillDataSet, Boolean catchException)
at Microsoft.SqlServer.Management.Common.ServerConnection.GetExecuteReader(SqlCommand command)
--- End of inner exception stack trace ---
at Microsoft.SqlServer.Management.Common.ServerConnection.GetExecuteReader(SqlCommand command)
at Microsoft.SqlServer.Management.Smo.ExecuteSql.GetDataReader(String query, SqlCommand& command)
at Microsoft.SqlServer.Management.Smo.DataProvider.SetConnectionAndQuery(ExecuteSql execSql, String query)
at Microsoft.SqlServer.Management.Smo.ExecuteSql.GetDataProvider(StringCollection query, Object con, StatementBuilder sb, RetriveMode rm)
at Microsoft.SqlServer.Management.Smo.SqlObjectBase.FillData(ResultType resultType, StringCollection sql, Object connectionInfo, StatementBuilder sb)
at Microsoft.SqlServer.Management.Smo.SqlObjectBase.FillDataWithUseFailure(SqlEnumResult sqlresult, ResultType resultType)
at Microsoft.SqlServer.Management.Smo.SqlObjectBase.BuildResult(EnumResult result)
at Microsoft.SqlServer.Management.Smo.DiskFile.GetData(EnumResult erParent)
at Microsoft.SqlServer.Management.Sdk.Sfc.Environment.GetData()
at Microsoft.SqlServer.Management.Sdk.Sfc.Environment.GetData(Request req, Object ci)
at Microsoft.SqlServer.Management.Sdk.Sfc.Enumerator.GetData(Object connectionInfo, Request request)
at Microsoft.SqlServer.Management.Sdk.Sfc.Enumerator.Process(Object connectionInfo, Request request)
--- End of inner

I tried to add all agent service users as sysadmin, didn't work.
The funny thing is that the job, under the maintenance plan, appears on the TSX.
When i create a standalone job and push it to the target, the job appears on the TSX(?!).
Hope someone can help.
Thank you in advance.


texts: SQLCMD -L Returns Bizarre Response Instead of Server List

texts:
I have a server with three installed SQL Server 2014 instances with Service Pack 1 (build number is 12.0.4213.0) installed. When I run SQLCMD -L on the server I am getting the bizarre response below.

  C:\Users\Tester>sqlcmd -L

  Servers:
      ;UID:Login ID=?;PWD:Password=?;Trusted_Connection:Use Integrated Security=?;
  *APP:AppName=?;*WSID:WorkStation ID=?;

The SQL Server is running normally and the SQL Browser service is started. Querying the SQL Browser service with another tool on the same server yields the expected results. I can run regular SQL queries with SQLCMD and get the expected results.

I was able to verify that the output above is the same on a workstation where I caused SQLCMD -L to receive no responses at all by enabling the firewall; however, the firewall is not an issue on this server. I've checked the settings and, as stated previously, my other tool gets SQL Browser Service responses to a broadcast just fine.

Also, running WHERE reports that the path to SQLCMD is C:\Program Files\Microsoft SQL Server\Client SDK\ODBC\110\Tools\Binn\SQLCMD.EXE, which is correct per File Locations for Default and Named Instances of SQL Server.

Does anyone have any idea why SQLCMD is doing this and how to fix it?


texts: I'm using a query that calculates some values on a table with about 11 millions rows. And I need to display the results in real time (on my site), but this calculations need about 1min to execute. The table content changes each 30 mins, so I don't have to recalc the results at each time user reloads the page. How can I cache the results of calculations? Via php (I use odbc) or using some sql statement, some sybase IQ option. Thanks.


texts: Is it possible to have more than one replica on a PostgreSQL server?

texts:
version: PostgreSQL 11.4
OS: Windows 10

This is an illustration of my question:

On 3 different machines there are one primary server per machine.

Is it possible to have one common server where replicas of these primary servers are?

When configuring a replica, I am instructed to delete the current database (the PGDATA directory), if there is one. Thus my question. If I already have a replica on that database, it is then deleted?

Do I have to have several installations of PG? Having them on their own VM's?


texts: I'm moving from SQL Server to Teradata, therefore could someone recommend any good (preferably online) resources about Teradata? I'm not looking for information about basics, but rather about new features and concepts that are in Teradata.


texts: DBCC UPDATEUSAGE (dbname) runs successfully but does not fix the "disk usage report"

texts:
I ran the DBCC UPDATEUSAGE command on our Dev box to see if it fixes the 'disk usage report'. It ran quickly ( 10 seconds) and ran successfully but did not fix the report. Any ideas about what the issue could be?
Thanks!


texts:
I need to show the average weight of a product over its last 4 production runs. I'm not sure how best to describe it other than an example: Let's imagine I have the following table that lists a product by date it was created, and the average weight of the product for that day:

+---------+---------+--------+
| Product |  Date   | Weight |
+---------+---------+--------+
|  900000 | Jan 1   | 20.0   |
|  900000 | March 3 | 12.2   |
|  900000 | July 6  | 15.0   |
|  900000 | July 7  | 14.0   |
|  900000 | Aug 6   | 3.0    |
|  800000 | June 2  | 14.0   |
|  800000 | June 3  | 12.0   |
+---------+---------+--------+

The end result I'm looking for is to add a column that includes the average Weight for the last 4 dates the product was run for, so something like this:

+---------+---------+--------+----------------+
| Product |  Date   | Weight | Average Weight |
+---------+---------+--------+----------------+
|  900000 | Jan 1   | 20.0   | NULL           |
|  900000 | March 3 | 12.2   | NULL           |
|  900000 | July 6  | 15.0   | NULL           |
|  900000 | July 7  | 14.0   | NULL           |
|  900000 | Aug 6   | 3.0    | 15.3           | Jan1+Mar3+July6+July7/4
|  900000 | Aug 8   | 13.0   | 11.05          | Mar3+July6+July7+Aug6/4
|  800000 | June 2  | 14.0   | NULL           |
|  800000 | June 3  | 12.0   | NULL           |
|  800000 | June 4  | 12.0   | NULL           |
|  800000 | June 5  | 12.0   | NULL           |
|  800000 | June 6  | 12.0   | 12.5           | etc...
+---------+---------+--------+----------------+

The NULL's are just there since in this sample you can't calculate the average over the last 4 runs because the data isn't there.

Could anyone point me in the direction I need to be looking to do something like this?


texts:
What does row size mean in mysql? I have a mysql db and table in myisam and it shows

Space usage Type    Usage
Data    1.3     GiB
Index   429.1   MiB
Total   1.7     GiB

Row Statistics Statements   Value
Format  static
Collation   utf8_unicode_ci
Rows    22,000,001
Row length ø    63
Row size ø  83 B
Next autoindex  22,000,002
Creation    Jul 06, 2014 at 10:48 AM
Last update     Jul 06, 2014 at 10:54 AM
Last check  Jul 06, 2014 at 10:54 AM

I don't understand what does the row size  mean. I tried searching on Google, but didn't find much explanation about it.

Can I use it to get the final table size after inserting N rows, by doing

row size * N rows  = table size ?

Is it correct?

Let's say I am in testing phase, so I dont have much data in my table. Can this formula be used to get the table size after inserting n rows while in production phase?


texts: Optimizing syncing of large dataset between 2 servers with SSIS

texts:
I have two DBs on different servers, Server A and Server B. Both are SQL Servers. I need to keep second DB in sync with the first one. DB schema and tables are identical, but Server A periodically creates new records that are missing on Server B. I decided to use SQL Server Integration Services, which is included in SSDT tools.
The simplest way to sync Server B with Server A is by creating SSIS project in Visual Studio that will generate Package.dtsx.
Here is what the package does currently

create OLE DB source referencing Server A
create OLE DB destination referencing Server B
pick specific table that needs to be synced
create column mapping for this table between Server A and Server B
in SSMS, add SQL Server Job that will run Package.dtsx every 15 minutes

This package seems to download all the records from Server A and tries to insert them on Server B.
The issues

Some records may already exist on Server B. Is there a way to insert only records that are missing on Server B, to prevent a bunch of errors in the log about duplicated primary keys?
Server A may have a lot of records. Is there a way to get the difference between servers BEFORE synchronization starts, maybe there is some action that can compare records by IDs and download only the difference between servers?


texts: SET TIME ZONE '-7' vs. AT TIME ZONE '-7'

texts:
This is similar to these questions:

Why does PostgreSQL interpret numeric UTC offset as POSIX and not ISO-8601?
Strange UTC offset time zone parsing in Postgres

I'm running Postgres 10.3. As per the docs, many time zone descriptors are interpreted as POSIX, which calculates negative offsets east of UTC while ISO 8601 interprets negative offsets west of UTC. But then this:

SET TIME ZONE '-7';
SHOW TIME ZONE;
>> <-07>+07
SELECT NOW();
>> 2018-09-26 12:00:20.309182-07
SELECT NOW() AT TIME ZONE 'America/Los_Angeles'
>> 2018-09-26 12:00:22.445955
SELECT NOW() AT TIME ZONE '-7'
>> 2018-09-27 02:00:27.862773

It appears that '-7' is interpreted as ISO 8601 when used in SET TIME ZONE '-7' and as POSIX when used in AT TIME ZONE '-7'.

Is there a reason for this? Is it documented somewhere?


texts: Can send test database mail but job emails failing

texts:
I stopped receiving sql agent job failure notifications in the recent past. Upon investigation the error reported is:

The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 1 (2017-08-16T02:40:14). Exception Message: Could not connect to mail server. (No such host is known).

The mail account setup has not been changed and I verified it looks correct. Futhermore, I can successfully send and receive an email using the Send Test Email... function in SSMS. So why can't the jobs send the emails out?

Since the Send Test Email... function queues the email, it doesn't seem as there would be a permissions issue with sending it this way vs the job sending it. Also, that would indicate it is not a firewall issue. So the only difference is time of day...the jobs run in the middle of the night...but I can't think of any system configuration that would be changing then.

Any other ideas of things to check?


texts: MySql Audit Changes from Db Admin, Security

texts:
I know that you can use triggers (before insert, update, delete) to track the changes in history tables. But this only works if the changes are made by the applications.

But in some circumstance, also the database admins will like to make a criminal act and change some database data, in order to to achieve personal benefits.

My question is, how can I protect/audit the database from the changes that can be manually done by the database admins? (They have the full rights over the database)


texts: SMO : The login already has an account under a different user name, when calling TransferData()

texts:
I need to implement the following requirement. I have setup a remote sql server express, certain users shall be able to create databases on remote server and assign other users to have access on it  (read,write). Users shall be able to see only the databases created by them or in which they have rights.
First I have created an empty database on remote server => dbo for new database is x, then  I've created a different login with:
USE [master]
GO
CREATE LOGIN [username] FROM WINDOWS WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english]

Then deny view to any database:
USE master;
GO
DENY VIEW ANY DATABASE TO [username];

Then create new user for login:
USE dbName;
CREATE USER [username] FOR LOGIN [username] ;
GO

Give db owner role:
EXEC sp_addrolemember 'db_owner', [username]

Give permission to see database (here is a little bit tricky because in ssms it can view all the databases but it can have access only on the mapped ones)
USE master GRANT VIEW ANY DATABASE TO [username];

Now on my given database I have 2 users one is username and the other is dbo which is mapped on the user that created the database and is different form username.
After that I'm using SMO to transfer this databases from remote server to localhost and while executing transferDatabase.TransferData(); I'm getting the following error "The login already has an account under a different user name." When I create the transfer instance I set also the property CopyAllUsers= true.
How can I fix this?
I've found out that if i make the following changes
USE [master]
GO
CREATE LOGIN [username] FROM WINDOWS WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english]

Then deny view to any database:
USE master;
GO
DENY VIEW ANY DATABASE TO [username];

USE master;
GO
ALTER AUTHORIZATION ON DATABASE::Dinesh TO [username];
GO

Now username sees in ssms only the databases on which is mapped and the transfer will probably work. But now I cannot distinguish between the first creator of the database and the recently granted user. For one database I should grant dbowner permissions to more than one user.
So if then I create another login and I execute:
USE master;
GO
ALTER AUTHORIZATION ON DATABASE::dbName TO [anotherUser];
GO

Now username can not access the database.


texts: pg_restore and psql asks password for restoring dump file

texts:
This will be probably extreme novice question but I want to restore a .backup dump file. I have tried the following command:

pg_restore -i -h localhost -p 5432 -U postgres -d old_db -v C:/blabla/bla.backup

First error I have encountered because the dump file is in text format so I should restore it using psql.

Then I have tried this command:

psql dbname < C:/blabla/bla.backup

But didn't accept the password of my windows user password


texts: Moving MySQL database from UniServer to Ubuntu Server?

texts:
I have set up Ubuntu Server 11.10, and plan to host my website on it. Until this point I was using UniServer to host it in a portable fashion. Using the built in MySQL. This is fine, and through testing, I have gathered a decent amount of info in my database that I really don't want to have to re-enter.

The problem is; that I cant find any help online to install the existing databases into MySQL on the Ubuntu side of things. I can Export the existing databases from UniServer, no problem. But once I've copied them over to Ubuntu, how do I get MySQL to read them.

For anyone that doesn't know, Ubuntu Server is a Command Line Interface OS. While I do have a Debian Graphical User Interface installed (accidentally), it doesn't allow much to be done graphically, other then file system search, copy, cut, paste, etc. Terminal application, and a minimalist web browser.

Any help that can anyone can give here is greatly appreciated. I am a Linux newbie, so simplified jargon is also appreciated, but I am very eager to learn!


texts: Find which objects are using my table column in the given schema

texts:
Lets say I have a table 'MyTable' having a column 'MyColumn'. I want to find out which procedures, triggers, views, functions are using that column. Currently I have this script which gets most objects with the given name. Please let me know if this script returns all the objects using the given column. Is there in-built function for this? Thanks

CREATE procedure [dbo].[Find] (@q varchar(255))
as

BEGIN
(SELECT DISTINCT
    o.name AS Object_Name,o.type_desc AS 'Type', m.definition AS 'Complete Name'
    FROM sys.sql_modules        m
        INNER JOIN sys.objects  o ON m.object_id=o.object_id
    WHERE m.definition Like '%'+@q+'%')

UNION ALL

(SELECT t.name AS Object_Name,'Table' AS 'Type', c.name AS 'Complete Name'
FROM sys.tables AS t
INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
WHERE c.name LIKE '%'+@q+'%')

UNION ALL

SELECT OBJECT_NAME(id) AS Object_Name, 'Trigger' AS 'Type',text AS 'Complete Name'
FROM syscomments
WHERE [text] LIKE '%'+@q+'%' AND OBJECTPROPERTY(id, 'IsTrigger') = 1

ORDER BY 2,1,3;
EnD


texts: PostgreSQL 9.1 vs MySQL 5.6 with InnoDB ? Which is better for scaling?

texts:
PostgreSQL 9.1 vs MySQL 5.6 with InnoDB. I do know that PostgreSQL offers better SQL features.

I am currently using MySQL 5.6 lab release (the one with InnoDb full text search feature)(not in production). I still find some features missing especially WRT GIS features, activation of triggers for on cascade deletion etc.

I have been testing out PostgreSQl 9.1 and am impressed by the support it has for GIS(PostGIS 2.0).

Now, Which of the above is better for scaling? Scaling out as well as Scaling up?

I need to scale for :

around 100k users(the users will increase)
with about 500 concurrent users(will increase say 1000-10000 concurrent).
The DB has about 2 million entries(constantly accessed) right now which will increase.
With constant writes/reads to the table with 2 million entries right now.
SQL, right now, is not too complex with a join to a max of 3 tables.
I am thinking of horizontal as well as vertical scaling.


texts: SQL Server 2008 Installation pointers for optimizing Performance and Scalability

texts:
We are working for a client with an asp.net/SQL server application which has been using a SQL Server 2005 system until now.

The client has decided to upgrade the database to SQL Server 2008 R2 and has procured a powerful server machine with 16 processors with enough RAM and installed Windows Server 2008 64 bit on the system.

Now we are tasked with the installation of SQL Server 2008 on the system followed by migration of the existing DB from the SQL 2005 box.

Now I am not a SQL Server DBA nor am I a System Administrator by role. Hence, in spite of reading up a lot of the literature on the net, I am not quite able to make sense of it all or put it in the right order of execution. Can somebody explain me the following items, preferably in terms of bullet lists:

What are the points of consideration during installation of SQL Server 2008, specifically in terms of

Configuring memory usage
Configuring the SQL Server to take advantage of the multiple processors available
Other factors to configure to enable SQL Server 2008 features for performance and scalability
Other points, if any, for configuring the Windows Server 2008 with respect to SQL Server 2008

Note: We will only use the Database Engine services in SQL Server. The applications does not use SSIS, SSRS, etc.


texts: Migrating all MySQL databases from from one server another. Getting error during import

texts:
I create a mysqldump on all the databases on the source server with this command:

  mysqldump -h mysql-01 -u root -p --all-databases > newdump.sql

This takes about an hour, but runs fine.

Now, to migrate the data of the dump file on the destination server I run this:

  mysql -h mysql-vm-01 -u root -p < newdump.sql

all seems to be going fine for like 30 minutes and then I get this error

  ERROR 1449 (HY000) at line 100083 : The user specified as a definer ('dc_reporter'@'%') does not exist.

What should I do ??


texts: Integer comparison in MySQL. does it cause lots of overhead?

texts:
I have an 8 digit number in my db. I want to do a select on the table in MySql (Innodb) and get the list of all numbers that are equal or smaller than my number. Then I sort them from larger to smaller.

I store the number as Integer.
Let's say my number is : 12040670

and after the select I get this list :

10340070
12005608
.
.
.
etc

Then I sort them from large to small.

If my table contains 1,000,000 records, and I run such comparison, do you think
it causes lots of load on the db server ? How about indexing the column ? Does
indexing help in lowering overhead when it comes to integer comparison ?


texts: is there a way to improve this query by not re calculating functions? cross apply?

texts:
when I set up replication I like to take a count of each table in the publisher, then I go to the subscriber I do the same thing in order to compare both pictures.

this is the picture from the publisher:

in order to generate this picture I run the following query:

USE ORCASTG_CA18_Repl
go
SELECT
         Publication=P.name
        --,Publication_description=p.description
        ,p.immediate_sync
        ,p.allow_anonymous
        ,p.replicate_ddl
        ,the_schema=OBJECT_SCHEMA_NAME(a.objid)
        ,TableName = a.name
        ,DestinationServer=s.srvname
        ,DestinationDB=s.dest_db
        ,DestinationSchema= a.dest_owner
        ,DestinationTable = A.dest_table
        ,Radhe='exec sp_count ' +'''' + QUOTENAME(OBJECT_SCHEMA_NAME(a.objid)) + '.' + QUOTENAME(a.name) + ''''

FROM dbo.syspublications P

INNER JOIN dbo.sysarticles A
        ON P.pubid = A.pubid

INNER JOIN dbo.syssubscriptions s
        ON a.artid = s.artid

WHERE 1=1
  AND s.dest_db <> 'virtual'

and that produces the following script:

exec sp_count '[dbo].[repl_application_placement]'
exec sp_count '[dbo].[repl_ApplicationCalendarRequest]'
exec sp_count '[dbo].[repl_ApplicationChecklist]'
exec sp_count '[dbo].[repl_ApplicationFlightDetail]'
exec sp_count '[dbo].[repl_ApplicationFlightLegDetail]'
exec sp_count '[dbo].[repl_ApplicationFlightReference]'
exec sp_count '[dbo].[repl_ApplicationProfile]'
exec sp_count '[dbo].[repl_ApplicationRequestFlightOption]'
exec sp_count '[dbo].[repl_ApplicationSkill]'
exec sp_count '[dbo].[repl_ApplicationVisaDetail]'
exec sp_count '[dbo].[repl_camp_profile]'

My question is:

Is there a way I can calculate the following line without re-running those functions:

,Radhe='exec sp_count ' +'''' + QUOTENAME(OBJECT_SCHEMA_NAME(a.objid)) + '.' + QUOTENAME(a.name) + ''''

I thought about cross apply!


texts: Will MySQL be effective when selecting a row from a table with billions of records?

texts:
Note: this is a hypothetical scenario

Lets say that:

I'm running MySQL, InnoDB engine, UTF-8 encoding
I have a table with 50+ billions of rows
The tables primary key is a BIGINT
The table also has 4 other columns, 3 of which are indexed (a BIGINT, TINYINT, and a VARCHAR(20)) ... (the unindexed column is type TEXT)

My questions are:

How fast would the queries be when selecting a row via primary key, or via indexed columns? What kind of hardware/server/s would be needed in order to make these queries execute faster than 0.1s?
Should I be using some other storage engine for this?


texts:
I found the following talk by Zardosht Kasheff enlightening:

http://www.infoq.com/presentations/Index-SQL

but it only covers the case of simple queries on a single table.

Is there something similar for queries involving multiple tables?  Possibly including GROUP BY?

I'm looking for some theory/guidelines which I can apply to MySQL with the InnoDB storage engine.


texts: Deploying SSRS reports on an "Air Gapped" report server without SSDT?

texts:
Whats the simplest way to deploy multiple reports (.rdl) along with the respective data sources to a machine which cannot be connected to from Visual Studio?

I can only place files at a given location, connect to the report server via remote desktop and execute them. I am not permitted to open firewall ports, or establish any other sort of connectivity between my machine and the report server.

One way is to manually upload the files one at a time, which I wish to avoid.


texts: Does varchar(n) affect the number of records which can be stored per page even if you store less than n?

texts:
I'm struggling to find enough documentation to help me answer my own question.

The common story seems to be that it doesn't make a difference to actual database size when you use a much higher value for n than will ever be used.

As an extreme example:
VARCHAR(5000) but only storing on average a small number of characters such as 4 characters.

That seems to be correct from a few tests I've run.

I've heard people saying that MySQL will take the higher value into account when managing pages meaning a potential for less records per page and that this can affect performance when running queries.

I can't see anything in the documentation to suggest this is actually the case so really looking for some clarity from a more experienced DBA.

So thanks in advance for any help. Please let me know if my question needs to be clearer.  My question is, does using a higher value for varchar(n) than will be stored affect the number of records which can be stored per page even if you store less than n?

I'm pretty new to this so if possible I'd also really appreciate a steer on how I can determine how many records are included in pages and then run some benchmarks.


texts: What should I do with mongodb security installed in aws ec2?

texts:
I using aws linux freetier

When I entered my site today, mongodb all collection was drop... OTL

(I checked the global log and confirmed that the Chinese ip dropped...)

When I installed mongodb, my /etc/mongod.conf setting

# network interfaces
net:
  port: change my port num
  #bindIp: 127.0.0.1
security:
    authorization: enabled

next i was aws security group open and write my port num and anywhere setting

Is this a problem? What should I add more?
Please teach me


texts: Finding road segements which are not split at intersection.(Query Optimization )

texts:
I have a query in which I am trying to find the road segments which intersect but are not splitted at intersection,I have also created gist index on geom column. Here is postgerSQL srcipt

SELECT a.id touched, b.id touching,  ST_Intersection(a.geom, b.geom)
FROM   pak_roads a, pak_roads b
WHERE ST_Crosses(a.geom, b.geom) and a.id < b.id and (a.MODE IS NULL
and b.MODE IS NULL )
and (a.grade_t IS NULL and a.grade_f IS NULL)
and (b.grade_t IS NULL and b.grade_f IS NULL) OR
-- "vertical" T bar, touching
(
 -- The "vertical" start node touches, but not on either of the "horizonal" nodes
 ST_Equals(ST_Intersection(a.geom, b.geom), ST_StartPoint(b.geom))
 AND NOT ST_Equals(ST_StartPoint(a.geom), ST_StartPoint(b.geom))
 AND NOT ST_Equals(ST_EndPoint(a.geom), ST_StartPoint(b.geom))
 ) OR (
 -- The "vertical" end node touches, but not on either of the "horizonal" nodes
 ST_Equals(ST_Intersection(a.geom, b.geom), ST_EndPoint(b.geom))
 AND NOT ST_Equals(ST_StartPoint(a.geom), ST_EndPoint(b.geom))
 AND NOT ST_Equals(ST_EndPoint(a.geom), ST_EndPoint(b.geom))
 )

Logic is working fine but it takes a lot of time to complete this task, can someone let me know how can I optimize this query.


texts: What are the consequences of setting varchar(8000)?

texts:
Since varchar takes disk space proportional to the size of the field, is there any reason why we shouldn't always define varchar as the maximum, e.g. varchar(8000) on SQL Server?

On create table, if I see anyone doing varchar(100) I should tell them no you are wrong you should do varchar(8000)?


texts: Granting password protected role to another role problem in Oracle

texts:
I can grant a role to another role unless the granted role is password protected, even when I know the password.  I keep getting "ORA-00990: missing or invalid privilege" The system privileges the Schema Owner of the roles has are RESOURCE, DBA and CONNECT.

grant OBLAH_DEE IDENTIFIED BY "15gx9FR7GG" to "OBLAH_DAH"


texts: Slow search times with functions despite index?

texts:
I'm aware that this question might be under the guidelines of being closed, but I'm out of ideas at the moment on how to optemize this table any further.

Given a have a few billion records with roughly 150 million a day, I assume search times will be high, but the following takes almost an hour to complete on descent hardware:

SELECT * FROM table WHERE time BETWEEN '2014-06-16 00:00:00'::timestamp AND '2014-06-17 00:00:00'::timestamp;

The table consists of the following:

CREATE TABLE messages (id BIGSERIAL, time TIMESTAMP WITH TIMEZONE, message varchar(120));
CREATE INDEX idx_time ON table(time ASC);
GRANT SELECT,INSERT,UPDATE ON table TO user;
GRANT UPDATE,USAGE,SELECT ON table_id_seq TO user;

Doing a direct query with psql -d database and selecting a range where i know there's no data it returns 0 rows instantly.. so the index is fine, i think..
So.. I've created a procedural function to return values one by one, but according to sources these functions doesn't utelize indexes at all(?), which might be true because the process to deliver records are quite slow and I think this is where my issue is, hughe bulk of data being returns to slow with a indexation not working on functions.

The function looks like:

DROP FUNCTION IF EXISTS myfunc(refcursor);
CREATE FUNCTION myfunc(refcursor) RETURNS refcursor AS $$
BEGIN
    OPEN $1 FOR SELECT * FROM table WHERE time BETWEEN '2014-06-16 00:00:00'::timestamp AND '2014-06-17 00:00:00'::timestamp;
    RETURN $1;
END;
$$ LANGUAGE plpgsql;

Any ideas why the massive bottlenecks? except the aparent that there's many records to deal with? The only reason for not reconstructing a better table structure where data is separated per day for instance is convenience right now.. So that's a planed change, but for the time being is there any way to make the search times go faster?


texts:
I have a server that is primarily used for SSAS, heavy processing, however I have SQL Server on it alongside SSAS, but I don't want sql server to hold ssas back.

I have set the SQL Server MAX memory to only 4 GB, and I don't have any large user databases on it.

TempDB also has been kept to 4 files, low profile.

Should I enable the server features that I normally do, like
lock pages in memory and Perform Volume Maintenance Tasks for sql server?

As you can see on the picture below
CPU, memory and disk space are relatively OK, but the processing will also be high.

I am really looking for some technical info, so that I can evaluate things up and apply to each situation.

  Lock Pages in Memory

  This Windows policy determines which accounts can use a process to
  keep data in physical memory, preventing the system from paging the
  data to virtual memory on disk. Locking pages in memory may keep the
  server responsive when paging memory to disk occurs. The SQL Server
  Lock Pages in Memory option is set to ON in 32-bit and 64-bit
  instances of SQL Server 2014 Standard edition and higher when the
  account with privileges to run sqlservr.exe has been granted the
  Windows "Locked Pages in Memory” (LPIM) user right.

These are examples of problems I had while managing SSAS from another server:

Linked Server from SQL Server to SSAS

routine to backup ssas databases

How to verify a SSAS Backup and how to perform Integrity Checks in SSAS


texts:
There are some negative numbers displayed in the InnoDB Engine Status output.
Anybody has encountered this or know why this happens ?

----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 1467883704; in additional pool allocated 26603520
Dictionary memory allocated 7134560
Buffer pool size 79552
Free buffers 0
Database pages 74624
Modified db pages 15997
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages read 12607789, created 151074, written 3737768
-7.21 reads/s, -0.17 creates/s, -1.10 writes/s
Buffer pool hit rate 1000 / 1000
--------------
ROW OPERATIONS
--------------
1 queries inside InnoDB, 0 queries in queue
6 read views open inside InnoDB
Main thread id 7652, state: sleeping
Number of rows inserted 10757341, updated 2461678, deleted 1189294, read 7916897438
-14.00 inserts/s, -5.18 updates/s, -0.00 deletes/s, -14185.96 reads/s

I am using MySQL Version 5.1.51 (64 bit) on Windows Server 2008


texts: Stored procedure deadlock on another process

texts: Is it possible to reproduce a deadlock situation on a stored procedure (which executes about 10 other stored procedures) when you don't know the exact process it's being deadlocked on? When this is called by the system it tends to deadlock but I have problems reproducing the error.


texts: What data type for a column which can have three states: yes, no, unknown

texts:
I have an application where the user can decide between:

yes
no
unknown

I started to use a boolean column which is nullable.

But things get quite complicated since my framework (django) does not provide a input field for coding a search form out of the box.

In the search form I need four states:

yes
no
unknown
show all (ignore this column in the search)

Related question: https://stackoverflow.com/questions/38307866/search-form-for-nullbooleanfield

An old rule of thumb comes to my mind again:

  Avoid nullable db columns.

I want to change the data type from a nullable boolean field to something different.

What data type for a column which can have three states: yes, not, unknown?

I use Postgres 9, but AFAIK this should not matter for this question.


texts: How do I reduce the database size on AWS RDS MySQL?

texts:
I have an RDS MySQL database on Amazon Web Services.

I want to reduce the size of the database in the control panel in order to save money.

Q: How do I make it smaller? Their site says you have to do it some magic way (from backups or such), but I don't want to mistakenly whack it!

Step by Step please... Thanks!

This is what happened: Amazon asked what my desired max size should be. I chose 10GB.  I then added data until the table exceeds 10GB.  Amazon then automatically increased the size limit to 20GB.  In their manual, they state "increasing" is automatic, but "decreasing" is not.

After removing some of the excess data so the data size is under 10GB again, is there a way to make a new 10GB db and copy the data to it using a snapshot perhaps?


texts: Rows missing after online conversion from MyISAM to InnoDB

texts:
We have a reasonably small database that we wanted to convert from MyISAM to InnoDB. Being database noobs, we just converted (using alter table) without even taking the site down.

Now that the conversion is done, a lot of intermittent rows seem to be missing. Is this possibly due to operations during conversion? Or is the issue somewhere else?


texts: How to debug a db memory-leak causing mysql to go before it's own limits?

texts:
We are having a problem with one of the database server of one application, possibly caused by some code that is creating a problem in the way Mysql manage it's memory.

Until the second week of April, our db server has a stable consumption of memory of about 5 gigs (with a maximum of 7 gigs). But then, it started to increase limitless, even surpassing it's theorically maximum possible allocation.

This is our yearly munin graph showing the increase in the last 2 months:

(source: postimg.org)

This is another view from the last seven days after a restart in mysql:

(source: postimg.org)

This is the report created by mysqltuner.pl:

-------- Performance Metrics -------------------------------------------------

[--] Up for: 4d 1h 56m 28s (152M q [431.585 qps], 383K conn, TX: 593B, RX: 29B)
[--] Reads / Writes: 90% / 10%
[--] Total buffers: 5.3G global + 10.2M per thread (200 max threads)

[OK] Maximum possible memory usage: 7.3G (46% of installed RAM)
[OK] Slow queries: 0% (2K/152M)
[OK] Highest usage of available connections: 13% (26/200)
[OK] Key buffer size / total MyISAM indexes: 16.0M/300.0K
[OK] Key buffer hit rate: 100.0% (61M cached / 9 reads)
[OK] Query cache efficiency: 70.8% (103M cached / 146M selects)
[!!] Query cache prunes per day: 501819
[OK] Sorts requiring temporary tables: 0% (926 temp sorts / 3M sorts)
[!!] Joins performed without indexes: 39128
[OK] Temporary tables created on disk: 16% (821K on disk / 5M total)
[OK] Thread cache hit rate: 99% (26 created / 383K connections)
[!!] Table cache hit rate: 10% (845 open / 7K opened)
[OK] Open file limit used: 3% (148/4K)
[OK] Table locks acquired immediately: 99% (65M immediate / 65M locks)
[!!] InnoDB data size / buffer pool: 5.5G/5.0G

We are in unknown territory here. Any help will be appreciated!

Edit: Adding my.cnf

# The MySQL database server configuration file.

[client]
port            = 3306
socket          = /var/run/mysqld/mysqld.sock

[mysqld_safe]
socket          = /var/run/mysqld/mysqld.sock
nice            = 0

[mysqld]
character_set_server = utf8
collation_server = utf8_general_ci

user            = mysql
socket          = /var/run/mysqld/mysqld.sock
pid-file        = /var/run/mysqld/mysqld.pid
port            = 3306
basedir         = /usr
datadir         = /var/lib/mysql
tmpdir          = /tmp
skip-external-locking
bind-address            = 0.0.0.0

# Fine Tuning
max_connections         = 200
key_buffer              = 16M
max_allowed_packet      = 16M
thread_stack            = 192K
join_buffer_size        = 2M
sort_buffer_size        = 2M
read_buffer_size        = 2M
read_rnd_buffer_size    = 4M
thread_cache_size       = 128
thread_concurrency      = 24
table_cache             = 2K
table_open_cache        = 2K
table_definition_cache  = 4K

# This replaces the startup script and checks MyISAM tables if needed
# the first time they are touched
myisam-recover         = BACKUP

# innodb
innodb_buffer_pool_size         = 5G
innodb_flush_log_at_trx_commit  = 1
innodb_support_xa               = 1
innodb_additional_mem_pool_size = 32M
innodb_log_buffer_size          = 8M
innodb_flush_method             = O_DIRECT

# Query Cache Configuration
query_cache_limit               = 32M
query_cache_size                = 256M
query_cache_min_res_unit        = 256

# Logging and Replication
log_error                       = /var/log/mysql/error.log
log-slow-queries                = /var/log/mysql/slow.log
long_query_time                 = 1

# REPLICATION CONFIGURATION
log_bin                 = /var/log/mysql/mysql-bin.log
log-bin                 = mysql-bin
expire_logs_days        = 15
sync_binlog             = 1
server-id               = 1

ssl-ca   =/etc/ssl/private/repl/cacert.pem
ssl-cert =/etc/ssl/private/repl/master-cert.pem
ssl-key  =/etc/ssl/private/repl/master-key.pem

[mysqldump]
quick
quote-names
max_allowed_packet      = 16M

[isamchk]
key_buffer              = 16M


texts: Stored Procedure null parameter within where clause

texts:
I want to use a parameter within where clause only if it's value is provided by strongly typed dataset, this is what I am trying at the moment, I get right results when I provide parameter3 and no results when I don't provide it's value.

What I desire is when I provide no value for parameter3, it should not use it in the query, as it's value is null, and I want to see all of the results in the query, not where Paramerter3 = null ones:

ALTER procedure [dbo].[GetData]
(
    @Parameter1 varchar(256),
    @Parameter2 varchar(256),
    @Parameter3 int = null
)
AS

SELECT
    *
FROM
    Table1
WHERE
    Table1.URL LIKE '%' + @Parameter1 + '%'
    AND Table1.ID = @Parameter2
    AND (@Parameter3 IS NULL OR Table1.ID2 = @Parameter3)
ORDER BY
    Table1.Title

Edit: I tried Thomas answer and executed like this:

EXEC @return_value = [dbo].[GetData]
                              @Parameter1 = N'asda',
                              @Parameter2 = N'asda',
                              @Parameter3 = null

SELECT  'Return Value' = @return_value
GO

I also updated the stored procedure as Thomas said.


texts: backup a specific set of tables to be moved to a remote system

texts:
I need to generate either backups or data dumps for about 30 tables.  I want to generate a file of some sort that contains the data for these tables and transfer them to a different system.  Each table has 3.8-4.5 million rows.  also a large number of these rows have columns with text that has newline characters which need to be handled correctly.

I'm using Sql Server 2012 and have SQL Server Management Studio.

Due to security and other particular reasons I cannot just use SQL Server Managment Studio's Import/Export Wizard for simply exporting tables from one database to another.  I have a requirement to get a dump of the data to transfer to a remote system.

I have tried SQL Server Management Studio's Generate Scripts capability, which does appear to handle the newline characters, however There is another issue.  In some of the fields the text is similar to "something blah \".  that slash causes a problem with the script that gets generated, because the Generate Scripts process wraps that text in ''  and with \' it basically escapes the single quote which corrupts the remaining data.

I have also tried to generate flat files via SQL Server Management Studio's Import/Export wizard and this doesn't appear to handle the newline characters correctly and again corrupting the remaining data.

Any help would be greatly appreciated.  and I can clarify any information that is missing.


texts: Return table with separate columns from function

texts:
I have this function, it's simple enough:

CREATE OR REPLACE FUNCTION test_function()
RETURNS TABLE(
        id INTEGER,
        organization_id INTEGER,
        email_address CHARACTER VARYING
)
AS $BODY$
        SELECT id, organization_id, email_address FROM users;

$BODY$ LANGUAGE SQL;

The output of this functions gives me something that looks like this:

> SELECT test_function();                                                                                test_function
---------------
 (1,1,test)
 (3,1,test2)
 (4,2,2test)

When I run the query by itself, I expect to get something more like this:

> select id, organization_id, email_address FROM users;
 id | organization_id | email_address
----+-----------------+---------------
  1 |               1 | test
  3 |               1 | test2
  4 |               2 | 2test

How can I get a similar result back like that so I can parse it in a SQL function?


texts: SSMS 2008 R2 no longer connects to database

texts:
Before Christmas I had no problem connecting SSMS 2008 R2 to a specific remote database. Since Christmas, I get this error when I try to connect:

  TITLE: Connect to Server

  Cannot connect to [IP ADDRESS].

  ADDITIONAL INFORMATION:

  An exception occurred while executing a Transact-SQL statement or
  batch. (Microsoft.SqlServer.ConnectionInfo)

  A transport-level error has occurred when receiving results from the
  server. (provider: TCP Provider, error: 0 - The semaphore timeout
  period has expired.) (Microsoft SQL Server, Error: 121)

  For help, click:
  http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=121&LinkId=20476

Any idea why this is happening? The link provided in the error message gives no information at all.

As far as I know, no updates or changes have been made to SSMS 2008 R2 or the database since the happy time when I could connect and now.

Any help much appreciated.


texts: Create folder in Sql Server management Studio 2008

texts: How can I group my stored procedure under folders in Sql server management studio 2008, Can I group and move related stored procedure to it.This should maintain the structure during database backup also.


texts: How Sql server 2012 join cardinality is calculated?

texts:
We have data warehouse based on SQL Server 2012. Often we get poor cardinality estimates for joins impacting downstream operators and slow execution, memory spills, etc.

Is there a good article or whitepaper or blog or video or something that explains how cardinality for joins are calculated? Update stats with full scan only helps for sometime/some cases.

Also any guidance links on how to remediate join cardinality errors would be very helpful.


texts: SQL Server 2008 R2 enterprise named instance

texts:
I have sql server 2008 r2 installed and I am using 3 named instances (2 express and 1 developer). For a task I need to use the CDC (change data capture) which is only in the enterprise edition. I have downloaded the trial from Microsoft website.

Given that I'm wondering if this should be done same as installing a new named instance or is it an upgrade?


texts: Any way to catch when xp_readerrorlog fails?

texts:
I'm trying to query the ErrorLog files, but it sometimes fails on some of my servers.
For some reason a file go missing from time to time. I do not know why. I do not think that I deleted the file :-).
Worse, SQL Server "crash" with this error

Msg 0, Level 11, State 0, Line 60
A severe error occurred on the current command.  The results, if any, should be discarded.

If I run

EXEC master..xp_readerrorlog 999

I do get a proper error:

Msg 22004, Level 16, State 1, Line 63
xp_readerrorlog() returned error 2, 'The system cannot find the file specified.'

I have tried many things; TRY-Catch block, a separate sproc to do the insert and dynamic SQL. They all eat my error message.

Here is my code:

SET NOCOUNT ON

DROP TABLE if EXISTS #ReportSQLErrorLogs

CREATE TABLE #ReportSQLErrorLogs (
    row_id BIGINT NOT NULL IDENTITY(1,1),
    log_date datetime NULL,
    processinfo nvarchar(16) NULL,
    ProcessText NVARCHAR(2048) NULL,
    CurrentLogNum TINYINT NULL
)

DECLARE @NumErrorLogs INT, @CurrentLogNum INT
SET @CurrentLogNum = 0

-- Get total number of log files from registry
EXEC master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'NumErrorLogs', @NumErrorLogs OUTPUT
SELECT @NumErrorLogs = ISNULL(@NumErrorLogs, 6) -- NULL in registry entry for Error Log files mean default of 6 value

WHILE @CurrentLogNum < @NumErrorLogs BEGIN
    INSERT INTO #ReportSQLErrorLogs ( log_date, processinfo, ProcessText )
    EXEC master..xp_readerrorlog @CurrentLogNum
    --PRINT @CurrentLogNum
    RAISERROR ('at %i', 10, 1, @CurrentLogNum) WITH NOWAIT

    UPDATE #ReportSQLErrorLogs SET CurrentLogNum=@CurrentLogNum WHERE CurrentLogNum IS NULL
    SELECT @CurrentLogNum = @CurrentLogNum + 1
END

And bonus points if you happen to know why errorlog files can go missing.


texts:
I'm writing my own Group by operator (non-hashed implementation) and I'm currently looking on a good data structure to store the result groups. There are two requirement in my mind: the table need to be efficiently expanded and the tuples within each group need to be sorted.

I carefully examined the implementation of standard Group by (both hashed and sorted implementation), the closest I got is to leverage parts of the TupleHashTable but before going through this plan, I need to make sure I'm not overlooking an existing data structure.


texts:
I am currently trying to find distinct combinations within a table as two of the columns have a many to many relationship with each other.

The data is all around backup policies being run on against particularly clients and could be summarized as below:

The answer I would like to generate for the above table would be 7 as there are that number of distinct combinations.

Has anyone got an idea of how this might be done? I have tried experimenting with nested counts and distinct values (where I was able to filter to one column but not both).


texts:
What is available in the realms of database modelling software that is free.  I am developing an SQLExpress database.

CA ERWin - community version
  although is free, I can't see an option to allow me to create a database, either using a direct connect or via creating an SQL script file.

Embarcardo ER/Studio - 14 day trial
  Can I reinstall this every 14 days?

What others are there, that will allow me to model my database and create a "create" script or "update" sql script?

Thanks in advance.


texts: Network Traffic increase with Small Result Set

texts:
I am currently executing a stored procedure that is using up a ton of network traffic when it executes.  The stored proc executes multiple stored procs within itself as well as functions and subqueries. There are 5 result sets returned each with 1 to 5 records, 10 columns max with VARCHAR(255).

My question is:

Does executing multiple Stored Procedures within another Stored Proc cause data to be returned to the client that you can't see, therefor creating network traffic? I thought everything was processed at server and then sent over network after its been processed.

Unfortunately we do not have the ability to use SQL Profile or to query System Tables since this DB is hosted by our provider.


texts: Query runs a long time in some newer MySQL versions

texts:
I have created a database on MySQL 5.0.15. I have a query and when I run this query on this MySQL version, I get 0.9 s run time. When I import this database to another MySQL server with same hardware and run the same query I get over 120s and sometimes MySQL hangs.

What is the difference between 5.0 and 5.1 or 5.5? I have tested 5.1 and 5.5 versions.

Is it possible a query takes longer in a newer version (something like mysql structure change)?

Sorry but I can't put this query here, but the query is like:

SELECT fl_passenger_ticket. *,
       fl_aganc.name                             AS agancname,
       fl_pnr.remark                             AS remark,
       fl_pnr.reservetime                        AS reservetime,
       fl_pnr.cancelpnr,
       fl_flight_date.fromcity                   AS fromcity,
       fl_flight_date.tocity                     AS tocity,
       fl_flight_date.flightdate                 AS flightdate,
       fl_flightdate_capacity.adultper           AS adultper,
       fl_flightdate_capacity.childper           AS childper,
       fl_flightdate_capacity.infantper          AS infantper,
       fl_flightdate_capacity.cancel             AS cancelsegment,
       fl_flightdate_capacity.tax1adultpric,
       fl_flightdate_capacity.tax1childpric,
       fl_flightdate_capacity.tax1infantpric,
       fl_flightdate_capacity.tax2adultpric,
       fl_flightdate_capacity.tax2childpric,
       fl_flightdate_capacity.tax2infantpric,
       ( fl_flightdate_capacity.tax3adultpric +
         fl_flightdate_capacity.tax4adultpric +
         fl_flightdate_capacity.tax5adultpric )  AS taxxtadultpric,
       ( fl_flightdate_capacity.tax3childpric +
         fl_flightdate_capacity.tax4childpric +
         fl_flightdate_capacity.tax5childpric )  AS taxxtchildpric,
       ( fl_flightdate_capacity.tax3infantpric +
         fl_flightdate_capacity.tax4infantpric
         +
         fl_flightdate_capacity.tax5infantpric ) AS taxxtinfantpric
FROM   fl_passenger_ticket
       INNER JOIN fl_pnr
         ON ( fl_passenger_ticket.pnrid = fl_pnr.pnrid )
       INNER JOIN fl_aganc
         ON ( fl_pnr.agancid = fl_aganc.agancid )
       LEFT JOIN fl_flightdate_capacity
         ON ( fl_pnr.pnrid = fl_flightdate_capacity.pnrid )
       LEFT JOIN fl_flight_date
         ON ( fl_flightdate_capacity.flightdateid = fl_flight_date.flightdateid
            )
WHERE  fl_passenger_ticket.ticketnumber <> ''
       AND fl_passenger_ticket.pnrid <> 0
       AND fl_pnr.agancid = 60
       AND fl_flightdate_capacity.aganccharterid = 0
       AND fl_flightdate_capacity.cancel IN ( 0, 1 )
       AND fl_pnr.reservetime >= '2011/09/01 00:00:00'
       AND fl_pnr.reservetime <= '2011/09/19 23:59:00'
ORDER  BY fl_passenger_ticket.rowid,
          fl_pnr.reservetime

I have 4 joins.
The table is innodb.
There are 100000 records

The result is 100 rows and 50 columns.

EXPLAIN result is

show variables like 'innodb%' result


texts:
What do you call the following expression?

(TableName == NULL) ? "0"  : TableName

I have a sense of what it does, but would like to look up the whole syntax definition, however as there is no real descriptor in the expression I can't seem to find out what it's called.


texts: AWS RDS MYSQL SP Privileges Issues (Error Code: 1227)

texts:
I have created SP in RDS-MYSQL with the user of abc. Now, I want to update from user xyz but its giving me message: (Note: Both user have full access on db)

Error Code: 1227
Access denied; you need (at least one of) the SUPER privilege(s) for this operation


texts: Best way to copy data to other location without blocking source

texts:
I have a production database with some data I need to copy to other location, either another database on the same server or another server.

The DB size is huge and I don't need all the data on the DB, but for instance in 1 of the tables with ~300 million records I need just ~60 million out of it.

The issue is that might not be possible to restore the entire DB in the same/another server because of space restrictions, so we might be forced to only copy what is needed.

And the other restriction is that these tables are used by production machines, so they need to be available 100% of the time, so I can't block any write and read access to them.

So, what would be the best way to copy partial data from one DB to anoter?

To run a query that inserts N records until it is done? kind of INSERT INTO ... SELECT TOP N ... FROM ... WITH(NOLOCK) WHERE ...
Use the Import and Export data? Will this tool block the source tables when running?


texts:
I'm trying to build a Windows cluster with 3 nodes, 2 Active and 1 Passive, And If I have to build in  a way like N1 Active - N3 Passive will be refer as "cluster network 1" and N2 Active and N3 Passive refer as "Cluster Network 2"..

So here my question is, can I have Node 3 tied to two different cluster networks?? I'm sorry if this a dumb question... But any suggestions would really help me..


texts: many of the same queries on the query plan - whats the pros and cons of Forced Parameterization in this scenario?

texts:
while working on finding all the queries in the query plan that are related to a particular index I realised that there were too many duplicates of the same queries  on the query plan.

Finding what queries in the plan cache use a specific index - Jonathan Kehayias

this populates a temp table with stats about my PK_application index:

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
IF OBJECT_ID('TEMPDB..#TheIndex') IS NOT NULL
   DROP TABLE #TheIndex;

CREATE TABLE #TheIndex (
[SQL_Text]      VARCHAR(max)                         NULL,
[DatabaseName]  VARCHAR(128)                         NULL,
[SchemaName]    VARCHAR(128)                         NULL,
[TableName]     VARCHAR(128)                         NULL,
[IndexName]     VARCHAR(128)                         NULL,
[IndexKind]     VARCHAR(128)                         NULL,
[plan_handle]   VARBINARY(64)                    NOT NULL,
[query_plan]    XML                                  NULL)
-- =============================================================================

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
DECLARE @IndexName AS NVARCHAR(128) = 'PK_application';

-- Make sure the name passed is appropriately quoted
IF (LEFT(@IndexName, 1) <> '[' AND RIGHT(@IndexName, 1) <> ']')
SET @IndexName = QUOTENAME(@IndexName);

--Handle the case where the left or right was quoted manually but not the opposite side
IF LEFT(@IndexName, 1) <> '[' SET @IndexName = '['+@IndexName;
IF RIGHT(@IndexName, 1) <> ']' SET @IndexName = @IndexName + ']';

--Dig into the plan cache and find all plans using this index
;WITH XMLNAMESPACES
   (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')

INSERT INTO #TheIndex
SELECT
stmt.value('(@StatementText)[1]', 'varchar(max)') AS SQL_Text,
obj.value('(@Database)[1]', 'varchar(128)') AS DatabaseName,
obj.value('(@Schema)[1]', 'varchar(128)') AS SchemaName,
obj.value('(@Table)[1]', 'varchar(128)') AS TableName,
obj.value('(@Index)[1]', 'varchar(128)') AS IndexName,
obj.value('(@IndexKind)[1]', 'varchar(128)') AS IndexKind,
cp.plan_handle,
query_plan
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS qp
CROSS APPLY query_plan.nodes('/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple') AS batch(stmt)
CROSS APPLY stmt.nodes('.//IndexScan/Object[@Index=sql:variable("@IndexName")]') AS idx(obj)
OPTION(MAXDOP 1, RECOMPILE);

--check what is in #theindex
select *
from #TheIndex

--add a primary key to the table #theindex
alter table #theIndex
add i int not null identity(1,1) primary key clustered

--checking duplicates by sql_text

select i=max(i),the_count=count(i),sql_text
from #TheIndex
group by sql_text
order by count(i) desc

--checking duplicates by plan handle

select i=max(i),the_count=count(i),plan_handle
from #TheIndex
group by plan_handle
order by count(i) desc

when I run sp_blitzcache I get:

sp_BlitzCache @SortOrder = 'recent compilations'

In this condition I have ruled out setting optimise for ad hoc workloads on

question:

Do I have a case for Forced Parameterization?

what could be the indications - pro and cons?


texts: How to figure out how to split a table using the proper scheme

texts:
If you have a big table that you want to partition and you have 2 candidates as the partitioning key (in the sense that 50% of the queries use one column and 50% of the queries use the other column and almost no query uses both in the same SQL query unless all the code is rewritten to do that) how can one determine which key is the best candidate to be used?
Does it matter for example if the "entity" that one key represents is "fixed" and the other is constantly increasing?
E.g. as an example of what I mean "growth" (taken just as an analogy of what I am asking) if you have decide between using the doctor id or the patient id as the partitioning key where we can have only so much doctors but infinite number of patients


texts: Is it possible to make a task always run, regardless of whether the package starts from scratch or from a checkpoint restart?


texts: How to test MySQL master-slave replication latency and get the exact time of slave library latency (accurate to milliseconds)

texts: How to test MySQL master-slave replication latency, I need to get the exact time of slave library latency (accurate to milliseconds)


texts: One-directional Transactional Replication is causing one table's Clustered Index to be copied over as a Nonclustered Index in the subscriber database?

texts:
This seems really odd to me, as I've used SQL Server's replication feature for a while now and have never seen this occur before.
I'm using One-Directional Transactional Replication and I have ServerA - the Publisher, and ServerB the Subscriber setup. I'm only replicating about 20 tables or so total.
In ServerA, Table1 has a couple of nonclustered indexes on it (including the primary key itself), and a separate clustered index. When the snapshot is generated to create Table1 on ServerB, all of it's indexes (even the clustered index) are created as nonclustered on the subscriber database in ServerB.
I even went so far as to take the following steps:

Removed Table1 from the Publication's Articles
Dropped Table1 from the subscriber database
Re-initialized and generated a new snapshot
Then re-added Table1 with the Article Properties set to false for "Copy nonclustered indexes"
Then re-initialized and generated a new snapshot again

And it still recreated Table1 on the subscriber database with it's clustered index as a nonclustered index. It did not copy any of its actual nonclustered indexes except the primary key (which again is defined as a nonclustered index in the publisher's database).
Here's some screenshots to show it (sorry for all the obfuscation):
Table1 in Publisher vs Subscriber Database:

Publisher Table1's sys.Indexes:
[
Subscriber Table1's sys.Indexes:

Publication's Article Properties for Table1:

What the heck is going on?


texts: mysqldump not escaping single quotes properly

texts:
I use mysqldump to backup my mysql database.
The problem is that the sql file generated by mysqldump doesn't escapes single quotes properly.

Here is an example of the mysqldump generated sql script :

INSERT INTO `someTable` VALUES (1,'This ain\'t escaped correctly');

That single quote escaping in "ain't" doesn't work and it makes the rest of the script being inside that string.
Is there a way around this?


texts: How can I monitor the call count per stored procedure per timespan?

texts: In order to diagnose some performance issues I would like to get a better understanding of the number of times certain procedures are called compared to the system performance. Is there a way to get the number of times each procedure has been called during a certain timespan?


texts:
I am using SQL Server 2008, RTM. I have a simple table defined as

create table t(id integer not null primary key)

This table has 50,000 records, just in case. When I issue an update transaction like

begin transaction
Update t
set id=50001
where id=5;

with no commit/rollback, it shows me one row affected. When I take a look at the result of the sp_locks, I and see there are two X locks on the KEY type granted, the Resource values for them are different from each other, the spid is the same.
Why there are two X locks on the key while there is only one row updated? Thanks.


texts: SSAS Dimension Help - Finding Values Not In the Dimension using Role Playing

texts:
I have a Fact / Fact Less table that has a list of Products likley to be purchased by client.

Client ID  Product ID
1          1
1          2
2          2
2          5
etc...

I need to to allow the users to find clients that have , from the example, the 2 product ID that does not have a 1 Product listed.    I get a file from the source system that is very simialr to the table above and I know I could create a table that looks like this below (assumign I only have a few products, and I do in this case...):

Client ID  Product ID  Listed
1          1           Y
1          2           Y
2          2           Y
2          5           Y
1          3           N
1          4           N
1          5           N
2          1           N
2          3           N
2          4           N

I could have two Role Playing Dimension based on the Listed flag that would all them to say show me all the clients that are Listed in 2 but not Listed in 1.  However, I'm sure there is a much better way.


texts: How can we sort the words inside a string in PostgreSQL?

texts:
In my postgresql table one column data is like below

250 TABLETS + PVC + PVDC + ALU + HOSPITAL PACK + Film-coated tablet - BLISTER - [/Tablet] - QUETIAPINE FUMARATE 28.78 mg is present
20 ML + MATERIAL UNKNOWN + Oral suspension - BOTTLE - [/mL] - ALUMINIUM HYDROXIDE 37 mg; MAGNESIUM HYDROXIDE 40 mg...

How can I split this string based on '+' character and sort this alphabetically and put | character and update the same column?


texts: How to give access to a user to a view, but not to the table the view is using?

texts:
I have a user (called myUser) which has db_datareader role on myCustomDatabase.

myCustomDatabase and MSDB are owned by sa user.

I've enable chaining (it's already enabled on MSDB):

ALTER DATABASE myCustomDatabase SET DB_CHAINING ON;

I created a view on myCustomDatabase as following:

CREATE VIEW myCustomDatabase.dbo.myView AS
    SELECT run_date  FROM msdb.dbo.sysjobhistory

I would like myUser be able to call this view, and not the following query:

SELECT * FROM msdb.dbo.sysjobhistory // Should not work
SELECT * FROM myCustomDatabase.dbo.myView // Should work

With described permissions I get following error:

The SELECT permission was denied on the object 'sysjobhistory', database 'msdb', schema 'dbo'.

Where am I wrong with db chaining?

Edit:

Here the script I run with succeed on a database test, but I still don't succeed to apply this changes on an already existing database:

CREATE DATABASE myCustomDatabase ;
GO

ALTER DATABASE myCustomDatabase SET DB_CHAINING ON;
USE myCustomDatabase ;
GO

CREATE VIEW dbo.myView AS
    SELECT run_date  FROM msdb.dbo.sysjobhistory
GO

// The login myUser already exists on my server
CREATE USER myUser FROM LOGIN myUser;

GRANT SELECT ON dbo.myView TO myUser;

Here the script I run on my already existing DB:

/*CREATE DATABASE thisDatabaseAlreadyExists ;
GO*/

ALTER DATABASE thisDatabaseAlreadyExists SET DB_CHAINING ON;
USE thisDatabaseAlreadyExists ;
GO

create  VIEW dbo.myView AS
    SELECT run_date  FROM msdb.dbo.sysjobhistory
GO

/* The user already exist, he's got db_datareader role on thisDatabaseAlreadyExists*/
/*CREATE USER myUser FROM LOGIN myUser;*/

GRANT SELECT ON dbo.myView TO myUser;


texts:
My fiddle is here.

Work around :
My table structure is

create table test (grIds int(1), amount int(1));
insert into test values
(1,10),
(1,20),
(1,15),
(2,0),
(3,8),
(3,25),
(3,16),
(4,0),
(4,21);

I have two select clause

select grIds, max(amount) from test
group by grIds having max(amount);

select grIds, max(amount) from test
group by grIds;

My question is why select with having keeps omitting row having grIds = 2 ?


texts: Entity Framework Core for SQL Server Failover Basic Availability Groups does not work

texts:
I have set up an SQL Server Standard 2019 with Basic Availability Groups on a Windows Failover cluster. It is not like the Always On AG, but more of a limited version of it where DB operations are on the primary replica and the secondary assumes the primary role only if there is a failover.
I have a .NET Core 2.1 application with two instances that runs in docker Windows containers on Windows Server 2019, on different VMs with SQL. Since our host does not have nested virtualization enabled, we are stuck with Windows containers. They connect to the SQL using the following connection string:
Server=TCP:172.18.18.11; Database=db; User Id=****; Password=****;
Connection Timeout=120;MultipleActiveResultSets=True;
Failover Partner=TCP:192.168.11.24

There are some weak points though. Since we had a small number of VMs and we use containers mostly, we did not care much for setting up the Active Directory due to overhead and some bad previous experience with Windows Container Networking (especially DNS issues) when using AD.
We have successfully created the Failover Cluster but they are on different subnets and different physical locations 172.18.18.0/24 and 192.168.11.0/24. This caused some problems with allocating the cluster IP resource, and since we do not have an internal DNS, we are stuck with IP addresses. And we cannot use cluster domain name or use the listener IP address since instances are on different subnets as well. This is a problem with our host and we could not really resolve.
So the problem is, SQL Failover works, but client applications do not get aware of the change, despite the "Failover Partner" in the connection string. In case of a failover, our app responds with a SQL Exception:

The target database, 'db', is participating in an availability group and is currently not accessible for queries. Either data movement is suspended or the availability replica is not enabled for read access. To allow read-only access to this and other databases in the availability group, enable read access to one or more secondary availability replicas in the group. For more information, see the ALTER AVAILABILITY GROUP statement in SQL Server Books Online. Cannot continue the execution because the session is in the kill state. A severe error occurred on the current command. The results, if any, should be discarded.

Instead I would expect it to direct the query to Failover Partner. I did some research and apparently specifying the failover partner in connection string works (not sure if it works for BAG or Always On though).

I tried this connection string:
Server=TCP:172.18.18.19; Database=db; User Id=****; Password=****;
Connection Timeout=120;MultipleActiveResultSets=True;
Failover Partner=TCP:192.168.11.31

The IPs here are the listener IP addresses, I get this message:

Server TCP:192.168.11.31, database GWP2 is not configured for database mirroring.

So it looks like Failover Partner keyword works as it connects to 192.168.11.31. However it looks like it requires mirroring, maybe Always On Availability groups rather than BAG?


texts: Get last ID of repeated values based on type and most recent sequence

texts:
As I am learning sql I am stuck on this one:

I have this table:

I am trying to get to this table:

The criteria is pick any ID that has Type A and its grade based on the most recent seq.


texts: Complex key implementation on nullable fields using unique indexes

texts:
Currently I have a table as follows:

ID    Name    Category    Group
 1     Abc       D1111    55501
 2     Abd       D1234    55502
 3     Abc       D1112    55545
 4     Abc       D1113    55545    // Group can repeat for distinct Name, Category
 5     Cdb       B3345     NULL
 6     Cdb       B3399     NULL
 7     RRR        NULL    12345
 8     RRR        NULL    23456

ID is my primary key. Name should never be null, however either of Category or Group (but never both) could be null. Additionally, (Name, Category) should always be unique and (Name, Group) should be unique where Category is null.

I'm wondering if implementing this with unique indexes is the best way to go or if I should somehow split this into more than one table. If I were to use indexes it might look like this:

CREATE UNIQUE INDEX index1 ON FakeTable (Name, Category)
WHERE Category IS NOT NULL;

CREATE UNIQUE INDEX index2 ON FakeTable  (Name, Group)
WHERE (Category IS NULL) AND (Group IS NOT NULL);

CREATE UNIQUE INDEX index3 ON FakeTable  (Name, Category, Group)
WHERE (Category IS NOT NULL) AND (Group IS NOT NULL);

Mainly this table is to provide a list of category/group combinations for each Name. So in that case I might query SELECT Category, Group FROM FakeTable WHERE Name = 'Abc'.

Also, I have other tables that reference this one via ID (although ID is a surrogate key, so in actuality it is Name/Group that usually serve as the reference fields).

I'm hoping someone can tell me if this is a bad strategy and why.


texts:
I would like to know if this is a good database for a blog:

This is a data model for a blog system that has the following characteristics:

Users can register
Users can have different roles, eg. author, administrator, commentator
Blog posts can be established with eg. headline, content, picture
Blog posts can belong to a number of categories
Blog posts can be marked with a number of tags
Blog posts can have different states, eg. drafts, published, archived, hidden

The data model (the design) has to include:

Entities with specification of attributes and keys
Relations with specification of cardinality and participation

This is for a school project, I am a beginner.


texts:
I've recently taken over administration of an availability group comprising two nodes, synchronous commit mode.

The configuration of the group looks like this:

My understanding is that, with the Readable Secondary option set to Yes on both replicas, any connection string with applicationIntent=ReadOnly will route to Node1.

Likewise, if I change Node2's Readable Secondary option to 'Read-intent', any connection string with applicationIntent=ReadOnly will route to Node2.

Why then, does this connection string route to Node2 when both nodes are set to 'Readable Secondary = Yes':

'Data Source=redacted.domain.com; Initial Catalog= MyDatabase; ApplicationIntent=ReadOnly; User Id=User; Password=********; MultiSubnetFailover=True'

Basically, changing the 'ReadOnly' parameter to 'ReadWrite' causes the connection to go to Node1. Changing back to 'ReadOnly' causes the connection to route to Node2. How is this possible without the 'Readable Secondary= Read-intent' option in place?

EDIT: output of
SELECT ag.name as "Availability Group", ar.replica_server_name as "When Primary Replica Is",
rl.routing_priority as "Routing Priority",
ar2.replica_server_name as "RO Routed To",
ar.secondary_role_allow_connections_desc,
ar2.read_only_routing_url
FROM sys.availability_read_only_routing_lists rl
inner join sys.availability_replicas ar on rl.replica_id = ar.replica_id
inner join sys.availability_replicas ar2 on rl.read_only_replica_id = ar2.replica_id
inner join sys.availability_groups ag on ar.group_id = ag.group_id
ORDER BY ag.name, ar.replica_server_name, rl.routing_priority

For anyone else needing help understanding this, I found the following helpful:


texts: FOR XML EXPLICIT problems in SQL Server 2005 Express

texts:
SQL Server 2005 Express Service Pack 2 w/Advanced Services

Windows 2003 R2

I recently migrated a database from MSDE 2000 to MSSQL 2005 that is used for a tourist website that can be displayed in one of three languages. A stored procedure is executed in ASP with a number that represents the language to use

Dim xml: xml = "<?xml version=""1.0"" encoding=""UTF-8""?>"

    response.write "<root>"
    set conn = server.createobject("ADODB.connection")
    conn.open application("bd")

    sqlTrad="EXEC psu_sitFicheTraduction @idLangue=1"
    set rsTrad = Server.CreateObject("ADODB.Recordset")
    rsTrad.Open sqlTrad, conn, 1,1
    do while not rsTrad.eof
        texte = rsTrad(0)
        response.write texte
        rsTrad.movenext
    loop
    rsTrad.close

The stored procedure EXEC psu_sitFicheTraduction runs the following SELECT query:

SELECT  1 AS Tag, NULL AS Parent,
        t.PK_txt AS [dicItem!1!ID],
        t.libelle_long_FR AS [dicItem!1!value],
        @idLangue as [dicItem!1!Langue]
FROM        txt_traduction t
FOR XML EXPLICIT

For some reason, on the new server, the XML is displayed incorrectly:

  This page contains the following errors:

  error on line 1 at column 51: PCDATA invalid Char value 1 Below is a
  rendering of the page up to the first error.

This is the source of the page:

  ɄIDՄvalueلLangue݄dicItemЁĂłP牵牥̂łༀ䌀慨扭敲搠栧瓴獥̂ł䈃ЁĂ奂ȀဂӤ䈃摲❩✬敍捲敲楤Ⱗ䨧略楤Ⱗ嘧湥牤摥❩✬慓敭楤崧̂łɲ䈃

When I run the stored procedure in SSMS, I don't get any errors although I have noticed that in Query Analyzer on the MSDE 2000 server I get 23 rows, where as in SQL Server 2005 SSMS I only get one big row.

This is the first time I've seen FOR XML EXPLICIT used so I don't have any experience with it.

Why is MSDE sending back 23 rows and SSMS only 1 big one
Any thing I need to configure in IIS to correctly display XML output coming from SQL Server ?

Any other suggestions or assistance would be appreciated.


texts: pivot a table with cell name as column name


texts: How should I best handle a rapidly growing database?

texts:
I have a database that I need to maintain.
Sadly, the setup and use of that database I can't change, much (thanks to some internal politics).
It's running on SQL Server 2008r2.
Its only been live for 5 days and has grown from 20GB to upwards of 120GB in that time. (essentially most of the data gets deleted and then imported, but like I say I can't control that side of things)
I would love to run nightly jobs to shrink the database and reorganise the indexes, but I know that's a long way from best practices and could lead to more problems than I've already got!
QUESTIONS

What's the best way to handle a database that's rapidly increasing in size?
Should I be looking at moving the file group around to keep the physical size on disk down?
Is there any way to stop the server running out of space within a month?