Reading data from oracle 8 in 2019 I'm an intern stuck dealing with a database system that is as old as I am.

The Issue:

I have a perl script pulling data from a server running Oracle 8 (8.1.7) and placing the data into a .csv.  The server that runs the script is being decommissioned and I am now need to port that script into a new server running a client for oracle 11 (11.2.0.4.0).  The problem is that when I run that script in the new server I get an error:

  failed: ORA-03134: Connections to this server version are no longer
  supported. (DBD ERROR: OCISessionBegin)

All servers are linux servers

The questions:

Documentation reagarding Oracle 8 is scarce but I'm assuming the error is happening because Oracle client 11 dosen't support oracle 8.  Assuming I can find a copy of client 8 is it possible to load 2 clients on one machine?
I could easily re-write the script in Java. The issue is figuring out how to find a driver that is compatible with Oracle 8, what is the latest version of OJDBC that supports oracle 8?

I'm open to other suggestions as to how to read data from Oracle 8. Using a server running Oracle 10 as an in-between is not an option

List of method (I know) to transfer data from one Oracle DB to another

database link
Data Pump (expdp/impdp)
exp/imp
SQL Developer -> Migrate Database
custom script ( eg PERL )
CSV/XML Dump and Import
ETL software (eg Informatica

The largest problem of any of those is "what is supported?".

*Database Link is only "supported" between a "2 major versions".  eg: 10g can talk to 8i but 11g can't.

Data Pump should support a lot larger range.  I don't think expdp was made available until 10g.

Classic Export/Import is limited on what is supported.  Luckily, Oracle Support has a document for "what works with what".  Ask your supervisors to get a copy of Doc ID 132904.1 for you.  YOU MUST HAVE A VALID SUPPORT CONTRACT TO ACCESS THIS DOCUMENT.

SQL Developer might help you out.  You'll need to import a JDBC driver that can talk to 8i.  You may have to contact Oracle Support to get that driver.  (latest is a 10g driver)

custom script can have problem if you cannot load two different drivers simultanously.

CSV/XML concept needs to be carefully thought out.  beware of \n.

ETL Software still needs the appropriate driver.

REMEMBER

10g is the latest that can talk to 8i.
11g is the latest that can talk to 9i.

Oracle Support has all the knowledge of "what works with what".

Update (2019-09-23)

Someone figured out how to import the Oracle 8i JDBC thin driver into an 18c database in order to run queries against Oracle 7.

https://community.oracle.com/thread/4268386

Have tried to access the Oracle 8i from a different DB (higher version) using DBlinks? Give the appropriate permission as high as possible (owner/dba) after creating the appropriate account(s) on Oracle 8i so that you don't change any changes to old accounts.

Document on DBlinks for Oracle 8i : https://docs.oracle.com/cd/A87860_01/doc/server.817/a85397/statem6b.htm


Why is cost threshold for parallelism ignored? SQL Server 2012 SP2 Enterprise Edition. Users complaining of slowness. Monitoring tool shows highest wait event is CXPACKET.

Instance settings

MAXDOP: 8
Cost Threshold for Parallelism: 175

Ran sp_BlitzCache (from the Brent Ozar toolset, those guys rock) for further diagnosis and results show queries with cost under 175 going parallel.

Anyone ever see Cost Threshold for Parallelism being ignored?

Anyone ever see Cost Threshold for Parallelism being ignored?

It is not being ignored. During the compilation process, the optimizer first considers a serial plan. If the estimated cost of that plan exceeds the Threshold, the optimizer goes on to look for a parallel plan. If the resulting parallel plan is costed below the best serial one, it will be chosen.

So, the parallel plan will have a lower cost that the serial one (which you cannot see). It is perfectly possible for the final parallel plan to have an estimated cost below the Threshold - the point is the best serial plan candidate exceeded the Threshold.

An example can be seen in my blog post on parallel plan bitmaps.

I,

It doesn't ignore!.
setting this parameter is only saying: The optimizer uses that cost threshold to figure out when it should start evaluating plans that can use multiple threads.
for example :
let say, your sql server got 4 threads, 3 of those threads are in use,soo only 1 left to use . if at this time you running your query, than it doesn't matter if you parameter is set to 5 or 500. in that case your query will get 1 thread (no parallelism) even if the optimizer will get COST of 1000 .


Does my m2n-table need a primary key? Two tables called Chip and Dale have m2n-relations to each other. I am used to build a link table called

CHIP2DALE (or Dale2Chip) containing:

id NUMBER UNIQUE NOT NULL
chip_id NUMBER NOT NULL
dale_id NUMBER NOT NULL

and an UNIQUE INDEX CHIP2DALE_UQ ON (chip_id, dale_id), because multiple relations don't make sense.

My question is: Do I still need the id column here? Is there any good reason for having this column? My rows are ultimatly identified by the combination of chip_id and dale_id GIVEN THAT I have absolutely no other columns here but the two.

You never ever need the id column in any table. It is a surrogate key which replaces the natural key for pragmatic reasons. Often this is because a narrow, monotonic key gives better performance, or because join clauses become cumbersome with multiple natural key columns.

Some shops have an id on every table as a standard. Consistentcy is good, so that would suggest having the id in this table, too. If this table is the parent of a 1:m relationship it can help performance to have the parent keys directly in the child table without having to follow surrogate keys.

It does take room on disk. If you have a gazillion rows that may be an important performance consideration. This would suggest omitting the id.

Broadly, in my experience it is best to omit the id from intersection tables, and add it later if the model changes.

Personally, I would have the id column in that table, and set it as an auto-incrementing integer, as that gives the ability to extend the content of that table by joining to another table that contains information about the relationship, such as when updates to relationships are performed, who performed them, etc.
Keeping the join table as small as possible improves the performance of joins if the data gets very large, and the additional table of relationship information can be joined in on an as-needed basis.
I've found this approach to be useful in the past of large (20+Tb) datasets.

Dave


How can one optimize a query involving a sort and a LIKE? Here is the query:

EXPLAIN ANALYZE SELECT
    *
FROM
    "Users" AS "User"
WHERE
    "User"."name" LIKE 'garr%'
AND "User"."id" NOT IN (2449214)
AND "User"."hellbanned" IS NULL
AND "User"."hellbanPostsAfterDate" IS NULL
ORDER BY
    "followerCount" DESC NULLS LAST
LIMIT 3;

Which yields:

Limit  (cost=0.43..3662.92 rows=3 width=1711) (actual time=181.935..2158.898 rows=3 loops=1)
  ->  Index Scan using users_search_followercount_index on "Users" "User"  (cost=0.43..280791.25 rows=230 width=1711) (actual time=181.933..2158.891 rows=3 loops=1)
        Filter: (((name)::text ~~ 'garr%'::text) AND (id <> 2449214))
        Rows Removed by Filter: 29434
Total runtime: 2158.951 ms

The index used is the most optimal I could build:

CREATE INDEX users_search_followercount_index ON "Users" ("followerCount" DESC NULLS LAST) WHERE "hellbanned" IS NULL AND "hellbanPostsAfterDate" IS NULL;

I also tried these:

-- CREATE UNIQUE INDEX users_name_unique_index ON "Users" ("name");
-- CREATE INDEX users_followerCount_index ON "Users" ("followerCount" DESC NULLS LAST);
-- CREATE UNIQUE INDEX users_search_name_followercount_unique_index ON "Users" ("name", "followerCount" DESC NULLS LAST) WHERE "hellbanned" IS NULL AND "hellbanPostsAfterDate" IS NULL; -- N/A
-- CREATE UNIQUE INDEX users_search_followercount_name_unique_index ON "Users" ("followerCount" DESC NULLS LAST, "name") WHERE "hellbanned" IS NULL AND "hellbanPostsAfterDate" IS NULL; -- 5422
-- CREATE INDEX users_name_unique_index ON "Users" ("name") WHERE "hellbanned" IS NULL AND "hellbanPostsAfterDate" IS NULL; -- 5422

But none speed it up.

Your index attempts are still futile, since the predicate

u."name" LIKE 'garr%'

is obviously the most selective one but cannot use a basic (default) b-tree index unless you are operating with the "C" locale (which is uncommon).

SELECT *
FROM   "Users"
WHERE  name LIKE 'garr%'
AND    id <> 2449214
AND    hellbanned IS NULL
AND    "hellbanPostsAfterDate" IS NULL
ORDER  BY "followerCount" DESC NULLS LAST
LIMIT  3;

There are several options (also depending on the missing table definition and other information), this index should work:

CREATE INDEX "Users_whatever_idx"
ON     "Users" (name text_pattern_ops, "followerCount" DESC NULLS LAST)
WHERE  hellbanned IS NULL
AND   "hellbanPostsAfterDate" IS NULL;

The conditions on hellbanned and "hellbanPostsAfterDate" are only useful if they exclude more than a few rows. Else, a complete index might be the better choice, since it can be used for more different queries.

Also, there is a chance the second column in the index "followerCount" won't help, since there are typically multiple different matches for the first and Postgres has to sort after all. Test a couple of typical queries with and without the second column in the index.

Details for indexes and pattern matching, including an explanation for the operator class text_pattern_ops:

Pattern matching with LIKE, SIMILAR TO or regular expressions in PostgreSQL

Aside: My standing advise is to use unquoted, legal, lower-case identifiers exclusively in Postgres.

Maybe you will try to use nested query...

select * from (SELECT
    *
FROM
    Users AS u
WHERE
    u."name" LIKE 'garr%'
AND u.id NOT IN (2449214)
) AS u
WHERE  u.hellbanned IS NULL
AND u.hellbanPostsAfterDate IS NULL
ORDER BY
    followerCount DESC NULLS LAST
LIMIT 3;


SSIS in Clustered Enterprise We have a 2-node clustered SQL Server 2014, Enterprise, environment for our data warehouse. Storage, network, and servers are all high performing (1tb mem, 32 cores CPU per node, all SSD)

We are trying to come up with where to put SSIS itself. Based on Microsoft documentation, it isn't recommended that it be clustered and, therefore, shouldn't be on the 2 nodes (though I did install it with the instances, we don't have to use if from there can could disable the services.) On the flip side, we don't really want to spend money on licensing of 8 vCPU's to put it on an available server in the same ecosystem (used for a proprietary ETL from vendor.)

What are the best practices and recommendations on how/where to install SSIS for a clustered SQL Server database environment?

Thank you,
Wes

EDIT: Microsoft discusses this here: https://docs.microsoft.com/en-us/sql/integration-services/service/integration-services-ssis-in-a-cluster but does not go over the recommended approach as an alternative.

The SSIS Catalog and SQL Server Agent are both part of the Failover Cluster Instance, and failed-over when a Failover Cluster Instance fails-over.  So my recommendation is to store all your packages in the SSIS catalog and schedule them all with SQL Agent.  And simply ignore the SSIS Service completely.

See
"The topics in this section discuss the Integration Services service, a Windows service for managing Integration Services packages. This service is not required to create, save, and run Integration Services packages. SQL Server 2012 supports the Integration Services service for backward compatibility with earlier releases of Integration Services. "
https://docs.microsoft.com/en-us/sql/integration-services/service/integration-services-service-ssis-service

SQL Server 2016 allows you to put the SSISDB into an AlwaysOn Availability Group.


Error 1290 despite of fixing my.ini file and having FILE permissions I tried loading data from a file but got ERROR 1290.

mysql> show variables like 'secure_file_priv';
+------------------+------------------------------------------------+
| Variable_name    | Value                                          |
+------------------+------------------------------------------------+
| secure_file_priv | C:\ProgramData\MySQL\MySQL Server 8.0\Uploads\ |
+------------------+------------------------------------------------+
1 row in set (0.01 sec)

mysql> load data infile "C:\ProgramData\MySQL\MySQL Server 8.0\Uploads\Book1.csv"
into table sample fields terminated by ',';

ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option
so it cannot execute this statement

I've aready tried the below link but didn't help.

    https://stackoverflow.com/questions/34102562/mysql-error-1290-hy000-secure-file-priv-option

I also have FILE privilege for the current_user.

That's how Book1.csv looks

1,A,A1
2,B,A2
3,C,A3
4,A,A4
5,B,A5
6,C,A6
7,A,A7
8,B,A8
9,C,A9
10,A,A10

I've spent hours. Pls help me out.

In windows, you need to give double slash "\" in the path.

   load data infile "C:\\ProgramData\\MySQL\\MySQL Server 8.0\\Uploads\\Book1.csv" into table sample fields terminated by ',';

You can give reverse slash "/" as below

   load data infile "C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/Book1.csv" into table sample fields terminated by ',';

Give it a try.

Check your separator in file book and the delimitor (or separator ) set by default in MySQL.
you can upload through cmd with command :

LOAD DATA INFILE ‘path/to/filename.csv’ INTO TABLE TABLENAME FIELDS
TERMINATED BY ‘,’ LINES TERMINATED BY ‘\n’
IGNORE 1 LINES; -- If you have line to ignore ;


Why do linked servers have a limitation of 10 branches in a CASE expression? Why does this CASE expression:

SELECT CASE column
        WHEN 'a' THEN '1'
        WHEN 'b' THEN '2'
        ... c -> i
        WHEN 'j' THEN '10'
        WHEN 'k' THEN '11'
    END [col]
FROM LinkedServer.database.dbo.table

Produce this result?

  Error message: Msg 8180, Level 16, State 1, Line 1 Statement(s) could not be prepared. Msg 125, Level 15, State 4, Line 1 Case expressions may only be nested to level 10.

Clearly there isn't a nested CASE expression here, though there are more than 10 "branches."

Another oddity. This inline table-valued function produces the same error:

ALTER FUNCTION [dbo].[fn_MyFunction]
(
     @var varchar(20)
)
RETURNS TABLE
AS
RETURN
(
    SELECT CASE column
            WHEN 'a' THEN '1'
            WHEN 'b' THEN '2'
            ... c -> i
            WHEN 'j' THEN '10'
            WHEN 'k' THEN '11'
        END [col]
    FROM LinkedServer.database.dbo.table
)

But a similar multi-statement TVF works fine:

ALTER FUNCTION [dbo].[fn_MyFunction]
(
    @var varchar(20)
)
RETURNS @result TABLE
(
    value varchar(max)
)
AS
BEGIN
    INSERT INTO @result
    SELECT CASE column
            WHEN 'a' THEN '1'
            WHEN 'b' THEN '2'
            ... c -> i
            WHEN 'j' THEN '10'
            WHEN 'k' THEN '11'
        END [col]
    FROM LinkedServer.database.dbo.table

RETURN;
END

Clearly there isn't a nested CASE expression here.

Not in the query text, no. But the parser always expands CASE expressions to the nested form:

SELECT CASE SUBSTRING(p.Name, 1, 1)
        WHEN 'a' THEN '1'
        WHEN 'b' THEN '2'
        WHEN 'c' THEN '3'
        WHEN 'd' THEN '4'
        WHEN 'e' THEN '5'
        WHEN 'f' THEN '6'
        WHEN 'g' THEN '7'
        WHEN 'h' THEN '8'
        WHEN 'i' THEN '9'
        WHEN 'j' THEN '10'
        WHEN 'k' THEN '11'
    END
FROM AdventureWorks2012.Production.Product AS p

That query is local (no linked server) and the Compute Scalar defines the following expression:

This is fine when executed locally, because the parser does not see a nested CASE statement over 10 levels deep (though it does pass one on to the later stages of local query compilation).

However, with a linked server, the generated text may be sent to the remote server for compilation. If that is the case, the remote parser sees a nested CASE statement more than 10 levels deep and you get error 8180.

  Another oddity. This inline table-valued function produces the same
  error

The in-line function is expanded in-place into the original query text, so it's no surprise the same error results with the linked server.

  But a similar multi-statement TVF works fine

Similar, but not the same. The msTVF involves an implicit conversion to varchar(max), which happens to prevent the CASE expression being sent to the remote server. Because the CASE is evaluated locally, a parser never sees an over-nested CASE and there is no error. If you change the table definition from varchar(max) to the implicit type of the CASE result - varchar(2) - the expression is remoted with the msTVF and you will get an error.

Ultimately, the error occurs when an over-nested CASE is evaluated by the remote server. If the CASE is not evaluated in the Remote Query iterator, no error results. For example, the following includes a CONVERT that is not remoted, so no error occurs even though a linked server is used:

SELECT CASE CONVERT(varchar(max), SUBSTRING(p.Name, 1, 1))
        WHEN 'a' THEN '1'
        WHEN 'b' THEN '2'
        WHEN 'c' THEN '3'
        WHEN 'd' THEN '4'
        WHEN 'e' THEN '5'
        WHEN 'f' THEN '6'
        WHEN 'g' THEN '7'
        WHEN 'h' THEN '8'
        WHEN 'i' THEN '9'
        WHEN 'j' THEN '10'
        WHEN 'k' THEN '11'
    END
FROM SQL2K8R2.AdventureWorks.Production.Product AS p

one way to get around this is to include the test in the when clause i.e.

case
  when SUBSTRING(p.Name, 1, 1) = 'a' THEN '1'
...


Any script to auto test tune and benchmark mysql config? I'm looking or willing to do a script to benchmark mysql config variations for a specific system.

A script that would change certain mysql config values and do a sysbench, save results, change config and restart service, and test again.

max_connections = A
table_open_cache = B
query_cache_type = C
innodb_buffer_pool_size = D
innodb_buffer_pool_instances = E
innodb_adaptive_hash_index_partitions = F

...

And the script does a sysbench looping through all possibilities that make sense:

on A takes values of 50 100 200 250
on B takes values of ....

I know it depends on system, but it would be great to have a recommended "set" of values / ranges to test. (Even check the current free RAM and do a small CPU bench and with this values create the set of values to test)

Is there anything outside that can acomplish this?

Would it be useful for you or it's not worth doing it?

Such a tool would be almost completely worthless in practical use.

max_connections has nothing to do with performance.  It's a safety mechanism to prevent a runaway application from making so many connections that the server becomes overwhelmed.  The value makes absolutely no difference unless you are using that many connections.  It is also a value that you should never reach under normal operating conditions.

table_open_cache I had hoped by now that everybody knew just to leave this one alone.  It tends to scale negatively -- the more "sensible" the value seems to be, the more wrong you are.  The worst performing servers in my entire network were the ones where someone, at some time in the past, had felt a need to mess with this.

query_cache_type just set it to 1, with a reasonable size, something in the 32 to 128 MiB range, and walk away.  You will be hard-pressed to develop a meaningful benchmark here, and a small query cache definitely has its value for many workloads.  But keep it reasonably small, or turn it off. Above a certain size, it will scale negatively and degrade performance.  The more processor cores you have, the more potential exists for the query cache mutex to become a bottleneck. For workloads where there is virtually no chance of a cache hit, it's a waste of cpu cycles.

innodb_buffer_pool_size has only one correct answer: How much memory can you spare?  Set that here.  Lower will always be slower, and higher will also be slower if the server goes into swap, and of course infinitely slower if it crashes because the system ran out of memory entirely.  Correlated to this, if you're running anything else on your database server, you are doing it wrong.

innodb_buffer_pool_instances As long as each pool is at least 1GB, there's not a lot of variation available or needed here on a decent sized server.

innodb_adaptive_hash_index_partitions is probably a poster child for an argument that applies in some sense to all of the other values above, and some not mentioned: even if you tweaked this to find a sweet spot with sysbench... who cares?  How does that help you?  It doesn't.

Your specific workload is the only thing really relevant to the ideal values for the adaptive hash index, the query cache, and so many of the other tweakable parameters on MySQL Server.

Given two servers with "identical everything" -- storage, memory, CPUs, background cosmic radiation levels -- identical everything, that is, except the workload (meaning the schemata, datafill, and the actual queries being run) the "optimum" configuration parameters may still be wildly divergent.

Benchmarking with anything other than a real live workload will not give you anything of significant value, and tweaking a server is not usually the ticket to performance.

Well-written queries against well-structured schemata and appropriately indexed tables, and use of read replicas, will take you much further than tweaking.

I have been using mysqltuner.pl from mysqltuner.com for years. It's not automatic, it analyses and makes recommendations.
Good for part time dba's like me.


How to model parent-child relationship correctly in multi-tenant, multi-schema Postgres database I have a multi-tenant SaaS app where each tenant gets their own schema. When a user creates an account, I create a new schema and run a bunch of migrations to create tables under that schema. Then, when the user wants to authenticate, they provide their tenant info (this can be their subdomain, e.g. mycompany.coolapp.com, or it can be a unique account ID), and the app routes subsequent queries to that tenant's schema.

However, certain pieces of tenants' data will be public. For instance, the user will need to create invoices and email their links (containing a UUID) to their customers. When the customer clicks the link, the app needs to be able to figure out which tenant the invoice belongs to, based on the UUID.

To do this, I created a "public.invoices" table, with two columns: uuid and tenant. The latter contains the tenant's schema name, which I can then use to perform the subsequent queries (i.e. the invoice's actual data) to the appropriate schema.

Question 1: Is this design sound? Is there some other, more standard way of routing unauthenticated/pre-authenticated requests to specific schemas in situations where each tenant gets their own schema?

Question 2: How do I tell Postgres that, when the tenant deletes the invoice (the one in their schema), the public record (under "public.invoices") also should be deleted? I'm trying to avoid using two queries, if some database-provided option or feature exists. I looked at CASCADE, but it seems that only goes from parent to child, and as far as I can tell there isn't a way to define a foreign key relationship in this scenario where the tenant invoice is the parent record.

There are many ways to implement this, but let me suggest partitioning.

The table public.invoice would then be list partitioned, with the tenant being the partitioning key. Each partition sits in the schema of the respective tenant.

That way everybody can query their own invoices in their own schema or all invoices in the public schema.

The big advantage of partitioning here is that it becomes very easy to remove a tenant: simple detach the respective partition and drop it.

Note that partitioning becomes impractical with thousands of partitions. But if you plan to have that many tenants, it is a bad idea to implement that with thousands of schemas with identical tables in a single database. Having too many tables will slow down metadata operations, and it makes life harder for autovacuum (which in turn will make your life hard).


How to build dimensions from SCD tables Having the foresight to begin collecting data from our production environment several years ago, I have a table with a following structure-

Part
Status
Startdate
Enddate

I can write a relational join, but how do I then go about including this in my project? What should I do in the DSV and in the Dimension relationships?

in the BIDS, open your project and double click on your DSV file, you can right click any where and add a table and then do the rest of steps like adding a relation

look at images

Step 1
Step 2

Is that what you meant

The proper technique is to relate the records in your relational tables. If done correctly, you do not need to to treat fact-dimension relationships any differently than non-SCD ones.


I can't restore my database on SQL Server 2012. Exceeds size I'm trying to restore a database from a .bak file. The file is only 89.3 mb large. I'm using the restore with replace option, so that all other data in the database is removed. We are just trying to get the latest data from out clients.

But I get an error saying that I will exceed my limit of 10GB. My server instance is, using

select @@version:

Microsoft SQL Server 2012 - 11.0.2100.60 (X64)
Feb 10 2012 19:39:15
Copyright (c) Microsoft Corporation Express Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)

Could someone please tell me why I can't get my database to restore. Do I have to get the full version of 2012? We are using the Express version

Express Edition limits the size of the database to 10 GB.

The backup is small because it only contains pages that are used -- regardless of whether or not the backup is compressed -- but the original file sizes will be restored to the size they were when they were backed up. The file sizes are what count against the limit.

Assuming this is the problem, the only solutions are to shrink the files before taking the backup, or restore the backup on a different edition (probably Developer edition).

SQL Server 2012 Express supports a maximum of 10GB, its possible that your database exceeds that limit.
You will need SQL Server 2012 Standard if your Database exceeds 10 GB

check this out


How to change default db2 database admin username to something else in Linux? I have a DB2 database version 11.1 on Linux - rhel 7.7 in my test environment. I need help on how to change the default db2 admin username to something else, ie. db2inst1 to dbroot. And the new db2 user - dbroot should have the same privileges which old db2 user - db2inst1 had. Please share the steps to do the needful tasks.

Thanks in advance

It is not possible to directly rename the db2 instance name, But following steps can be used to move the db to new instance

  Take the backup of DB's and provide 755 permissions to backup images. Offline backup is recommended
  db2stop command to stop the instance
  drop current instance name by using db2idrop command
  create new instance by using db2icrt command
  once instance is created, switch to the instance id and  please start the instance by db2start command
  Restore the db to new instance name by using below command incase of auto storage
   db2 restore db  from  ON  NEWLOGPATH
  If there are user defined tablespaces, Which will need redirect restore
  db2 connect to

or Run below command to grant DBADM access to dbroot.

db2 " grant DBADM WITH DATAACCESS on database to user dbroot";

You may want to create a new instance with instance name as dbroot and then move all your database to new instance dbroot (from current instance db2inst1) using db2relocatedb utility.

Please check: https://www.ibm.com/support/knowledgecenter/en/SSEPGG_11.5.0/com.ibm.db2.luw.admin.cmd.doc/doc/r0004500.html


Which index type is best suited for exact-match against a list of values? I have this table:

CREATE TABLE public.foos (
    id integer NOT NULL,
    bar_string character varying
);

I want to do this query:

SELECT id from foos where bar_string in ('one', 'two', 'three')

The size of the list will be something like 5-100.

If I know I'll only ever be doing this query, what's the best index to put on bar_string? I've read that Hash indexes are better than btree for exact matches, is this the way to go?

TL;DR: BTREE looks like it is a bit more efficient than HASH. You should read and adapt this test to your situation

Benchmark

I tried the two solutions for a geohash id column. Here is the script I used:

require 'set'

def random_geohash(precision)
  letters = %w(0 1 2 3 4 5 6 7 8 9 b c d e f g h j k m n p q r s t u v w x y z)

  letters.sample(precision).join('')
end

geohashes = Set.new

loop do
  geohashes << random_geohash(9)
  break if geohashes.length == 50_000
end

sample = geohashes.to_a.sample(1_000).map { |h| "'%s'" % h }.join(", ")

IO.write("perfs.init.sql", <<~SQL)
  DROP TABLE IF EXISTS "tests";

  CREATE TABLE "tests" (
  "id_btree" text NOT NULL,
  "id_hash" text NOT NULL
  );

  CREATE INDEX "index_id_btree" ON tests USING btree (id_btree);
  CREATE INDEX "index_id_hash" ON tests USING hash (id_hash);

  INSERT INTO tests (id_btree, id_hash) VALUES #{geohashes.map { |h| "('%s', '%s')" % [h,h] }.join(", ")};
  VACUUM ANALYZE;
SQL

IO.write("perfs.btree.sql", <<~SQL)
  EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS, FORMAT JSON)
  SELECT * FROM tests WHERE id_btree IN (#{sample});
SQL

IO.write("perfs.hash.sql", <<~SQL)
  EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS, FORMAT JSON)
  SELECT * FROM tests WHERE id_hash IN (#{sample});
SQL

IO.write("perfs.end.sql", <<~SQL)
  DROP TABLE IF EXISTS "tests";
SQL

`psql --user=john_doe -qAt -f perfs.init.sql my_db`
`psql --user=john_doe -qAt -f perfs.btree.sql my_db > perfs.btree.json`
`psql --user=john_doe -qAt -f perfs.hash.sql my_db > perfs.hash.json`
`psql --user=john_doe -qAt -f perfs.end.sql my_db`

This will give you two explain analyze (perfs.<index>.json) that you can now read. I suggest using Pev for a comfortable lecture. Here are the text results for PG11 on my mac:

BTREE

Index Scan using index_id_btree on public.tests  (cost=0.29..2611.50 rows=1000 width=20) (actual time=0.988..4.347 rows=1000 loops=1)
  Output: id_btree, id_hash
  Index Cond: (tests.id_btree = ANY ('{e6g8nf3kv,...,3r9n5udye}'::text[]))
  Buffers: shared hit=3014
Planning Time: 1.475 ms
Execution Time: 4.424 ms

HASH

Bitmap Heap Scan on public.tests  (cost=1179.75..2758.75 rows=1000 width=20) (actual time=1.784..15.275 rows=1000 loops=1)
  Output: id_btree, id_hash
  Recheck Cond: (tests.id_hash = ANY ('{e6g8nf3kv,...,3r9n5udye}'::text[]))
  Heap Blocks: exact=304
  Buffers: shared hit=1463
  ->  Bitmap Index Scan on index_id_hash  (cost=0.00..1179.50 rows=1000 width=0) (actual time=1.714..1.714 rows=1000 loops=1)
        Index Cond: (tests.id_hash = ANY ('{e6g8nf3kv,...,3r9n5udye}'::text[]))
        Buffers: shared hit=1159
Planning Time: 1.495 ms
Execution Time: 15.446 ms

Hash Index:

  Hash indexes are used only for equality comparisons that use the = or <=> operators. They are not used for comparison operators such as "<" that find a range of values.
  The optimizer cannot use a hash index to speed up ORDER BY operations.
  Hash indexes can only handle simple equality comparisons. The query planner will consider using a hash index whenever an indexed column is involved in a comparison using the = operator. Only whole keys can be used to search for a row.

B-tree index:

  With a B-tree index, any leftmost prefix of the key can be used to find rows.
  A B-tree index can be used for column comparisons in expressions that use the =, >, >=, <, <=, or BETWEEN operators. The index also can be used for LIKE comparisons if the argument to LIKE is a constant string that does not start with a wildcard character.
  Constructs equivalent to combinations of these operators, such as BETWEEN and IN, can also be implemented with a B-tree index search. Also, an IS NULL or IS NOT NULL condition on an index column can be used with a B-tree index.

So, Hash Index are faster for exact matches i.e, =. But can only handle simple equality comparisons cannot use to speed up ORDER BY operations.

As you are using IN in your query, you should use B-tree index.


How to recover the suspect mode database in SQL Server 2008? To recover the database from suspect mode executes the following query.

EXEC sp_resetstatus ‘yourDBname’;
ALTER DATABASE yourDBname SET EMERGENCY
DBCC checkdb(’yourDBname’)
ALTER DATABASE yourDBname SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DBCC CheckDB (’yourDBname’, REPAIR_ALLOW_DATA_LOSS)
ALTER DATABASE yourDBname SET MULTI_USER

Then getting

  Check terminated. A failure was detected while collecting facts.
  Possibly tempdb out of space or a system table is inconsistent. Check
  previous errors

How to resolve this?

According to Paul Randal, this seems to be a bug in checkdb. Check this article from Paul which discusses about this issue.

The workaround is to run CHECKDB on the database with TABLOCK

You have a database in MS SQL that is tagged as (suspect) and you are unable to connect to the database.

Possible Causes

The database could have become corrupted.

There is not enough space available for the SQL Server to recover the
database during startup.

The database cannot be opened due to inaccessible files or
insufficient memory or disk space.

The database files are being held by operating system, third party
backup software etc.

There was an unexpected SQL Server Shutdown, power failure or a
hardware failure.

Solution

Open Microsoft SQL Server Management Studio and connect to your database

Click the New Query button

Paste the following SQL script into your New Query page replacing [YourDatabase] with the name of your database.

EXEC sp_resetstatus [YourDatabase];
ALTER DATABASE [YourDatabase] SET EMERGENCY
DBCC checkdb([YourDatabase])
ALTER DATABASE [YourDatabase] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DBCC CheckDB ([YourDatabase], REPAIR_ALLOW_DATA_LOSS)
ALTER DATABASE [YourDatabase] SET MULTI_USER
Click Execute

Source : https://support.appzone.com/kb/a398/how-to-repair-a-suspect-database-in-mssql.aspx


MySQL sum of column group by question I have the results of a select that I am joining on that I need to get a slightly complex sum of
a column from.

I need the sum of result grouped on id PLUS the sum of result of any row where parent_id = id.

The data from the join:

cat_id   parent_id   name    result
------   ---------   -----   ------
1        2           name1   7
1        2           name1   1
2        4           name2   2

For example from the above I need a result like:

cat_id   name    sum(result)
------   -----   -----------
1        name1   8
2        name2   10

Any ideas much appreciated

SELECT id, name, SUM(result)
FROM (SELECT cat_id as id, name, result  FROM test
UNION ALL
SELECT t1.parent_id as id, t2.name, t1.result
FROM test as t1
LEFT JOIN test as t2 ON t2.cat_id = t1.parent_id
) as a
GROUP BY id,name ORDER BY id


Listener is not starting in my Standby database I'm facing a serious issue in data guard installation. I'm setting oracle_sid and oracle_home and when I start the listener,error is showing as below:

C:\Windows\system32>lsnrctl start
LSNRCTL for 64-bit Windows: Version 11.2.0.4.0 - Production on 31-MAY-2017 16: :38
Copyright (c) 1991, 2013, Oracle. All rights reserved.
Starting tnslsnr: please wait...
Failed to start service, error 0. TNS-12560: TNS:protocol adapter error

Solutions which I have tried:

I've set the correct path in environment variables which is in System Properties.
I've given correct IP address in Hostname(listener.ora)

But still nothing could help me out....It's urgent

Can you try the below,

run the cmd as administartor and verify the ORACLE_HOME and PATH variable and try lsnrctl start
If it still fails to start, provide listener.ora file


Inner Join or what else? What I have

Table A                                 Table B

ID    ID_Fruit    ,,, so on             ID      ID_Fruit   ID_CRATE  DateTime
1     1002                              i_1     1002          1      2014-07-28 00:00.000
2     4534                              i_2     1002          2      2014-06-25 00:00.000
3     1243                              i_3     1243          NULL   2014-07-09 00:00.000
4     1003                              i_4     4534          1      2014-07-23 00:00.000`

Result I want:

ID    ID_Fruit    ,,, so on             ID      ID_Fruit    ID_CRATE     DateTime
1     1002                              i_1     1002           2      2014-06-25 00:00.000
2     4534                              i_4     4534           1      2014-07-23 00:00.000

What I am trying,

SELECT *
FROM TABLEA
    OUTER APPLY
        (SELECT *
         FROM TableB
         WHERE TableB.ID_Fruit = TableA.ID_Fruit
           AND TableB.ID_CRATE IS NOT NULL
        ) As Table_B

I want only 1 row from several rows in Table B based on closest DateTime

Yes, shouldn't be too difficult, try:

SELECT a.ID, a.ID_Fruit,,, so on b.ID, b.ID_Fruit, b.ID_Crate, b.[DateTime]
FROM TABLEA a
INNER JOIN TABLEB b ON a.ID_Fruit = b.ID_Fruit
INNER JOIN (SELECT ID_Fruit, MAX([DateTime]) FROM TABLEB GROUP BY ID_FRUIT) maxtime
      ON b.[DateTime] = maxtime.[DateTime]

Its not quite clear if you mean that it should be the most recent, but I have assumed that is what you mean.

I get the suggestion of @blobbles and rewrite my response.

I divide my answer into two parts for convenience:

In the case of table A contains all the idFruit of table B or when data quantity are not so big.

WITH fruit AS (
    SELECT idFruit, MAX (dt) dt
    FROM B
    WHERE b.idCrane IS NOT NULL
    GROUP BY B.idFruit
)
SELECT *
FROM A, B, fruit
WHERE     a.idFruit = fruit.idFruit
          AND b.idFruit = fruit.idFruit
          AND b.dt = fruit.dt

IMVHO it's a more feasible solution.

This is the simplest case. In case of necessity it's possible add an HINT materialize in the with clause to increase performance.
In the case of table A that do not contains all the idFruit of table B.

I think the with block can reduce the resulting record set if the condition between A and B are present.

WITH fruit AS (
    SELECT B.idFruit, MAX (b.dt) dt
    FROM A, B
    WHERE a.idFruit = B.idFruit
          AND b.idCrane IS NOT NULL
    GROUP BY B.idFruit
)

P.S. I leave the explicit statement between A, B and Fruit instead of JOIN just because, to me, it seems easier to read, but I agree with what he says @blobbles in the comments below.


how to check who has killed the job which was running ?if logs are not generated Unspecified error occurred on SQL Server. Connection may have been terminated by the server.

This is error I am getting.(when the job is executed)

  [SQLSTATE HY000] (Error 0)  Cannot continue the execution because the session is in the kill state.
  [SQLSTATE HY000] (Error 596).  The step failed.

The error log should contain some information about what session killed a particular process.  Run the following statement to get the hostname and process id of the user the KILL command originated from.

EXEC xp_readerrorlog 0, 1, N'kill', NULL, NULL, NULL, N'DESC'

Note that if any time has gone by since the command was run, this may return stale results as session ids are reused.  Using the Host Process ID, you can then find the user:

select login_name, session_id, host_name, host_process_id
from sys.dm_exec_sessions where host_process_id = XXXX AND session_id = YY

Here's an example:

EXEC xp_readerrorlog 0, 1, N'kill', NULL, NULL, NULL, N'DESC'

LogDate                 ProcessInfo  Text
----------------------- ------------ ----------------------------------------------------------------------
2017-06-20 07:17:32.210 spid64       Process ID 62 was killed by hostname JOHNLAPPY, host process ID 7968.

(1 row(s) affected)

select login_name, session_id, host_name, host_process_id from sys.dm_exec_sessions where host_process_id = 7968 AND session_id = 62

login_name              session_id host_name  host_process_id
----------------------- ---------- ---------- ---------------
CONTOSO\john.eisbrener  62         JOHNLAPPY  7968

(1 row(s) affected)

I think, you need put process_info 64 as a session_id, 62 for was killed by 64.

select login_name, session_id, host_name, host_process_id from sys.dm_exec_sessions where host_process_id = 7968 AND session_id = 64

hopefully then you the get the out put result, otherwise it will show up nothing ...


Getting an error ORA-00933 while using EXECUTE IMMEDIATE Hi i am using 1 dynamic query which has a very big length size as below.

v_DymnamicQuery :=
    'WITH TempResult as
    (
        SELECT
        f.rbase_fmla_name as FilingName, f.user_comp_eff_dt, f.user_rnwl_dt, f.user_n_bus_dt, f.pf_wkstn_oid_lng, f.pf_wkstn_oid_sh, f.fmla_flng_u_exp_dt as expDate,
        rtrim(f.flng_origntr) ||'' | ''||rtrim(f.flng_lob)||'' | ''||rtrim(f.flng_regn) as FileTag,
        ''Formula'' as DisplayType, null as Origin, f.wkstn_oid_lng, f.wkstn_oid_sh, CAST(f.iteration_number AS varchar(10) )||''.''||CAST(f.minor_version AS varchar(10)) as version, status_indc, f.status_indc as WIPStatus,
        upper(RTrim(f.rbase_fmla_name) ||''-''|| CAST(f.user_n_bus_dt AS varchar(10)) ||''-''|| CAST(f.user_comp_eff_dt as varchar(10)) ||''-''|| CAST(f.user_rnwl_dt AS varchar(10)) ||''-''|| CAST(f.iteration_number AS varchar(10)) ||''-''|| rtrim(f.flng_origntr) ||'' | ''||rtrim(f.flng_lob)||'' | ''||rtrim(f.flng_regn)) as NameWithDates'
        || v_FmlaQuery ||
        ' from fmla_flng_adptn f '
        || v_FmlaCondition ||
        ' where '|| p_isFormulas ||'= 1 AND (rtrim(f.flng_origntr) ||'' | ''||rtrim(f.flng_lob)||'' | ''||rtrim(f.flng_regn)) = COALESCE('||v_FileTag||', (rtrim(f.flng_origntr) ||'' | ''||rtrim(f.flng_lob)||'' | ''||rtrim(f.flng_regn)))
        and f.rbase_fmla_name like ''%''|| COALESCE('||v_name||', f.rbase_fmla_name)||''%''
    union all
        SELECT
        f.rbase_rttbl_name as FilingName, f.user_comp_eff_dt, f.user_rnwl_dt, f.user_n_bus_dt, f.pf_wkstn_oid_lng, f.pf_wkstn_oid_sh, f.rt_flng_u_exp_dt as ExpDate,
        rtrim(f.flng_origntr) ||'' | ''||rtrim(f.flng_lob)||'' | ''||rtrim(f.flng_regn) as FileTag,
        (case f.rt_flng_type_indc when ''C'' then ''Constant Table'' else ''Keyed Table'' end) as DisplayType, null as Origin,f.wkstn_oid_lng, f.wkstn_oid_sh, CAST(f.iteration_number AS varchar(10))||''.''||CAST(f.minor_version AS varchar(10)) as version, status_indc, f.status_indc as WIPStatus,
        upper(RTrim(f.rbase_rttbl_name) ||''-''|| CAST(f.user_n_bus_dt AS varchar(10)) ||''-''|| CAST(f.user_comp_eff_dt AS varchar(10)) ||''-''|| CAST(f.user_rnwl_dt as varchar(10) ) ||''-''|| CAST(f.iteration_number as varchar(10)) ||''-''|| rtrim(f.flng_origntr) ||'' | ''||rtrim(f.flng_lob)||'' | ''||rtrim(f.flng_regn)) as NameWithDates'
        || v_TableQuery ||
        ' from rt_flng_adptn f
        where '|| p_isTables ||'= 1 AND rtrim(f.flng_origntr) ||'' | ''||rtrim(f.flng_lob)||'' | ''||rtrim(f.flng_regn) = COALESCE('||v_FileTag||', rtrim(f.flng_origntr) ||'' | ''||rtrim(f.flng_lob)||'' | ''||rtrim(f.flng_regn)) and f.rbase_rttbl_name like ''%''|| COALESCE('||v_name||', f.rbase_rttbl_name)||''%''
    ),
    UnionTable as
    (
        SELECT ROW_NUMBER() OVER(ORDER BY ' || p_SortColumn || ') as RowNumber,
        FilingName, user_comp_eff_dt, user_rnwl_dt, user_n_bus_dt, pf_wkstn_oid_lng, pf_wkstn_oid_sh, expDate,
        FileTag, DisplayType, Origin, wkstn_oid_lng, wkstn_oid_sh, version, status_indc, WIPStatus,NameWithDates '
        || v_UnionQuery ||
        ' from TempResult
    )
    SELECT *
    FROM UnionTable
    WHERE RowNum > '||v_FirstRec||'
    AND RowNum < '||v_LastRec||';
    --AND RowNum = '||v_LastRecForRow;

    dbms_output.put_line(v_DymnamicQuery);

    EXECUTE IMMEDIATE v_DymnamicQuery;

while running the EXECUTE IMMEDIATE it is throwing an error as:
ORA-00933: SQL command not properly ended
ORA-06512: at "RATABASE.GETFILINGSWITHPAGING", line 120
ORA-06512: at line 5

even when i am running the same query(string) which comes from
dbms_output.put_line(v_DymnamicQuery);
it is successfully running without any error.

can someone help me how to use this or handle this situation.

At the end of the string, you're concatenating a final closing semi-colon; however, you don't have a closing quote after it.

Instead of:

AND RowNum < '||v_LastRec||';

Try this:

AND RowNum < '||v_LastRec||';'


Arithmetic overflow error converting float to data type numeric I am trying to move data from a csv file into a SQL server database. Some of my values are in the scientific notation. I figured out on how to get most of them converted but for one value I get the Arithmetic overflow error.

The value that is causing the error is 4.56621E-6. If I change the part before the E by removing the 1 so it reads 4.5662E-6 the import works fine. All the other values I need to import work fine.

I use a format file to import the data. Below the line for the column that is giving me grief:

88  SQLFLT8 0   0   "," 89  PPL_2_BL    ""

The format in the database is decimal(18,9). Any suggestions on how to avoid this error without manually changing values in the source file?

To put it into perspective. The CSV file contains more than 2.2 million rows with 154 columns each. Which results in a CSV file size of more than 2GB. Currently I am working with a test file. When the final go live comes. I need to switch over fast. Which means I can not analyze and edit the file for several days.

Update

I played around with the values a little bit.

4.56621E-6   -> fails
6.5789474E-6 -> works
4.5662E-6    -> works
4.56622E-6   -> fails
4.566210E-6  -> works (surprisingly)
4.66621E-6   -> fails

I'm no expert in this, but it looks to me as if you're trying to use FLOAT. Have you tried using NUMERIC? Something like this maybe? Just throwing ideas out.

88  SQLNUMERIC 0   0   "," 89  PPL_2_BL    ""

Interesting Question!

Reference source from MS documentation here.

This appears to be a function of the format file-

Specifying a host-file length of 0 in the format file automatically defaults the length of the CSV field to the length of the database source file +1. Since the length past the decimal in the database is 9, .00000045662 fits (9+1)but .000000456621 does not (11).

Try setting the format file to:

88  SQLFLT8 0   12   "," 89  PPL_2_BL    ""

Unless you expect longer values to come through the field in the future, this should work.


slow select query on datetime column I have a table in MySQL which has approximately 10 million records, and now a query which used to take micro seconds now takes more that 8 seconds to execute!
Here is my table structure:

CREATE TABLE `runs` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `runstub` VARCHAR(20) DEFAULT NULL,
  `userid` INT(11) DEFAULT NULL,
  `processid` INT(11) DEFAULT NULL,
  `rundata` VARCHAR(255) DEFAULT NULL,
  `token` VARCHAR(60) DEFAULT NULL,
  `created_at` DATETIME DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `created_at` (`created_at`)
) ENGINE=INNODB AUTO_INCREMENT=10039971 DEFAULT CHARSET=latin1;

and here is the query that takes more than 8 secs...

SELECT MIN(created_at), MAX(created_at) FROM runs ORDER BY id DESC LIMIT 10000

Is there a way I can optimise this query to get it run in less than a second?

This is the result I want to achieve: From the last 10k records (not all), the time it took. I want the time it started (MIN(created_at)) and the time it ended (MAX(created_at)). How can I achieve that without the limit?

Here's one attempt:

SELECT MIN(created_at), MAX(created_at)
FROM (
    SELECT created_at
    FROM runs
    ORDER BY id DESC
    LIMIT 10000
) as x;

Your query is evaluated as:

SELECT MIN(created_at), MAX(created_at) FROM runs

Then it is ordered according to id (which doesn't make sense) and a maximum of 10000 rows is returned (only 1 row exists)

My query first picks the 10000 most recent id's

SELECT created_at
FROM runs
ORDER BY id DESC
LIMIT 10000

From there it picks min and max for created_at

if the query used to take microseconds and now takes minutes means you reach a limit... probably query buffer.

Check your my.cnf file and increase or set the following params

myisam_sort_buffer_size = 128M
max_connections         = 4096
sort_buffer_size        = 32M
read_buffer_size        = 256K
read_rnd_buffer_size    = 8M
wait_timeout            = 10
connect_timeout         = 5
interactive_timeout     = 10
concurrent_insert       = 2
thread_concurrency      = 0
tmp_table_size          = 64M
max_heap_table_size     = 512M


What happens if a delete query times out SQL Server for php? Technology

I am using the mssql extension to php (on linux) to send queries to SQL Server.

The Setup.

I have a table that builds up disposable data rather quickly. It took me longer than expected to build a cron to clean out this data every hour so it has been accumulating over months to be well over 1G of data not sure how many rows but probably over 1 million.

The Problem

So I ran a delete query for anything older than 1 day.

DELETE FROM CacheForms WHERE TimeStamp < 1503454349

However after 60sec the query times out and the script fails. (update the same happens at 10min.)

The Question

When this delete query times out (a php concept) what happens to the database? Does the database restore the deleted data from transaction log? or is the data truly deleted? Or does the query keep running in sql server?

When a timeout occurs, the client API sends an attention signal to SQL Server to cancel the executing query. Upon receipt, SQL Server cancels the query and rolls back the transaction. Data modification statements are run in autocommit mode by default so each statement is all-or-none.

Assuming PHP aborts/closes the SQL connection, SQL should rollback any work it had done in the delete.


Cannot log in using SA with SQL Authentication I can not log in to SQL Server 2008 using SQL Server authentication.

I can only log in using Windows Authentication Mode.

I chose only defaults while installing SQL Server 2008 R2.

Did you look anywhere? Right-click the server and choose Properties, then select the Security screen.

Then, log in using your Windows auth admin account, and set the password for the sa SQL auth account.

ALTER LOGIN sa WITH PASSWORD = 'strong password here';

Sometimes login is disabled due to many incorrect passwords or any policy violation. So what we can do is just log in with window authentication, change the password and enable login again.

Log into database with Windows Authentication

Run Query:

ALTER LOGIN [sa] WITH PASSWORD='newpassword', CHECK_POLICY=OFF
GO
ALTER LOGIN [sa] ENABLE
GO

Use of CHECK_POLICY is required as it specifies that the Windows password policies of the computer on which SQL Server is running should be enforced on this login. The default is ON when we make OFF it will change the password without any difficulties. Later you can ON

NOTE: What I have observed that when CHECK_POLICY is on the GUI SSME solutions for changing password won't work every time. I don't the reason behind but that I have observed.


HADR high worker thread usage Why would an availability group's number of worker threads in an HADR pool increase well beyond minimum usage of "typically, there are 3–10 shared threads" per replica?

In one case we've observed usage of 300+ threads with 3 availability groups and 10 databases total. SQL Server 2014 SP1.

Our leads are backup on secondary replica, high activity on primary replica, reports on secondary replica.

The AGs are in a datacenter on VMware. 16 schedulers total, usual worker threads are under 200 range. max_dop on server is 2.

3 AG, 10 DB, 4 replica each - primary, 2 readonly, 1 not readable.
1 secondary is synch, 2 async
16 vcores on 32 cores physical on large multi host cluster.
No overprovision.
Other smaller VMs 4-8 cores are colocated, but they don't press on CPU

We observed a spike in worker threads resulting in denial of service. Attribution of worker threads to AG is our assumption, as only those worker threads can cross the limit.

Below links from the SQL Server Premier Field Engineer Blog read in context don't give a complete answer to me:

Monitoring SQL Server 2012 AlwaysOn Availability Groups Worker Thread Consumption
Are my actual worker threads exceeding the sp_configure ‘max worker threads’ value?

Since your DC is on VM, I suspect you are experiencing poor disk performance. Poor disk performance can result in slower log-write times on the secondary which can result in a slower acknowledgment back to the primary replica from the secondary replica (exhausting worker threads).

Disk latency on Secondary Replica can cause an increase in HADR Sync Commit process, resulting in the Primary holding open threads while waiting for the Secondary to acknowledge the transaction.

Please check the error log for Deadlocked Schedulers and collect some IO metrics from PerfMon to see the disk latency and the disk queue length.


How to succesfully run a batch file in an SQL Agent job? I have a SQL Agent Job which generates a specific report in PDF-file and then copies the PDF to a network directory and then deletes the PDF file in the source directory.

The SQL Jobs consists of 2 steps:
1. Generate the report
2. Copy the report to the network location.

For step 2 I made a bat-file which handles the copying and removal of the pdf file.

The bat-file is as follows:

set source_dir=C:\Reports\Energie\Uur
set dest_dir=\\KWS2-WEB-SERVER\Share\Reports\Uur

C:\Windows\System32\Robocopy.exe %source_dir% %dest_dir% /MOV /Z

However, when I run my the Job, it hangs on the second step. The status just stays on "Executing".

This is the line which I stated in the step (location of the bat-file to execute):

cmd.exe /c "C:\Reports\rapport_uur_copy.bat"

My job-settings are as follows:

Step 1

Type: Operating system (CmdExec)
On Success: Go to the next step

On Failure: Quit the job reporting failure

Step 2

Type: Operating system (CmdExec)

On Success: Quit the job reporting success

On Failure: Quit the job reporting failure

Some facts:

I have read/write permissions on the network directory
I run the steps as the Administrator-account (the logged in user, default)
Step 1 succeeds
I run Windows Server 2008 R2 Standard
I have SQL Server 2008 R2
When I run the script from the cmd manually, it works (under Administrator account).

It looks like you need to configure the second step of your job to run as a domain account with permissions to write to the \KWS2-WEB-SERVER\Share\Reports\Uur share as well as read access to the C:\Reports\Energie\Uur folder.

You first will need to add a credential to SQL Server. From the Security > Credentials folder you will need to right click and choose New Credential... Fill in that information and click OK.

Once that is complete you will need to create a Proxy that uses this credential. From the SQL Server Agent > Proxies > Operating System (CmdExec) folder you will need to right click and choose New Proxy... Use the credential you created earlier.

Now you can configure your second job step to use the Proxy you just created using Run As: drop down of the job step page.

Try using COMSPEC to run the .bat using a .CMD file


Duplicate login details per user in error log Being able to view details of who successfully logs into a database and which user's login failed via the error logs is a good thing. However I have a scenario that I need help with:

For some reason the success and failure details are logged multiple times and as such if I login to a SQL Server 2008 R2 instance this very minute, I see multiple "Login successful for user ..." messages.

Can anyone help to resolve this (without turning off the option to save login success/failure) as it is filling up the logs and pushing down relevant information.

I realize this doesn't answer the question "why does the log file have multiple entries for 'Login succeeded...'", however since you also seem to be concerned about the other log entries being "drowned-out" by these low-importance login messages, I thought I'd add the following as an answer.

Instead of using SQL Server Management Studio's built-in interface for looking at log files, you can use the following code that will allow you to ignore log messages you are not interested in.  This allows you to concentrate on messages that may be more immediately relevant.

IF NOT EXISTS(
    SELECT s.name + '.' + t.name
    FROM tempdb.sys.tables t
        INNER JOIN tempdb.sys.schemas s ON t.schema_id = s.schema_id
    WHERE s.name + '.' + t.name = 'dbo.ErrorLogAnalysis'
    )
BEGIN
    CREATE TABLE tempdb.dbo.ErrorLogAnalysis
    (
        LogDate DATETIME,
        ProcessInfo NVARCHAR(255),
        [Text] NVARCHAR(MAX)
    );
END

TRUNCATE TABLE tempdb.dbo.ErrorLogAnalysis;
EXEC sp_ReadErrorLog 0,1;
INSERT INTO tempdb.dbo.ErrorLogAnalysis
SELECT ela.*
FROM tempdb.dbo.ErrorLogAnalysis ela
WHERE ela.Text NOT LIKE 'Login succeeded%'
ORDER BY LogDate DESC;

It may be helpful to point out that client-side code is quite likely to use more than a single connection to SQL Server.  This may in fact be the precise reason why more than a single entry is recorded in the SQL Server Error Log.

To prove this, I've written a very simple VB.Net command-line app that creates 5 connections to my local SQL Server instance.  Once all five connections have been opened, the app executes a simple query against the server showing 5 session_id values that the app has open.  It then closes all 5 connections, and exits.

This is the code:

Module Module1

    Sub Main()

        Dim sConnString As String =
            "SERVER=localhost;" & _
            "DATABASE=master;" & _
            "TRUSTED_CONNECTION=True;" & _
            "Application Name=MultiConnTest"
        Dim sqlC1 As New SqlClient.SqlConnection(sConnString)
        Dim sqlC2 As New SqlClient.SqlConnection(sConnString)
        Dim sqlC3 As New SqlClient.SqlConnection(sConnString)
        Dim sqlC4 As New SqlClient.SqlConnection(sConnString)
        Dim sqlC5 As New SqlClient.SqlConnection(sConnString)
        sqlC1.Open()
        sqlC2.Open()
        sqlC3.Open()
        sqlC4.Open()
        sqlC5.Open()
        Dim sqlCMD As New SqlClient.SqlCommand( _
            "SELECT session_id, login_name " & _
            "FROM sys.dm_exec_sessions des " & _
            "WHERE des.host_name = HOST_NAME " & _
            "   AND des.program_name = 'MultiConnTest';", sqlC1)
        Console.WriteLine("Testing multiple connections to SQL Server.")
        Console.WriteLine()
        Console.WriteLine("Sessions currently open on the server, with username")
        Dim sqlReader As SqlClient.SqlDataReader = sqlCMD.ExecuteReader
        Dim iSessionID As Int32
        Dim sUserName As String
        While sqlReader.Read
            iSessionID = sqlReader.GetValue(0)
            sUserName = sqlReader.GetValue(1)
            Console.WriteLine(iSessionID & vbTab & sUserName)
        End While
        sqlReader.Close()
        sqlCMD = Nothing
        sqlC1.Close()
        sqlC2.Close()
        sqlC3.Close()
        sqlC4.Close()
        sqlC5.Close()
        Console.WriteLine("Press any key to exit")
        Console.ReadKey()

    End Sub

End Module

The console output from running this against my machine is:

Testing multiple connections to SQL Server.

Sessions currently open on the server, with username
52      Max
53      Max
54      Max
55      Max
56      Max
Press any key to exit

The SQL Server Error Log, after enabling auditing of both successful and unsuccessful logins, shows:

LogDate                 ProcessInfo Text
2014-09-16 10:31:54.290 Logon       Login succeeded for user 'Max'. Connection made using Windows authentication. [CLIENT: <local machine>]
2014-09-16 10:31:54.290 Logon       Login succeeded for user 'Max'. Connection made using Windows authentication. [CLIENT: <local machine>]
2014-09-16 10:31:54.290 Logon       Login succeeded for user 'Max'. Connection made using Windows authentication. [CLIENT: <local machine>]
2014-09-16 10:31:54.290 Logon       Login succeeded for user 'Max'. Connection made using Windows authentication. [CLIENT: <local machine>]
2014-09-16 10:31:54.290 Logon       Login succeeded for user 'Max'. Connection made using Windows authentication. [CLIENT: <local machine>]

I think you can see from this a possible source of your error log entries is not some errant SQL Server behavior that can be "disabled", but is far more likely to be the desired result arising from the combination of auditing successful logins, and client applications that use multiple connections.

I would check the source code of whatever applications are running against your SQL Server to determine if they are in-fact using multiple connections.

If they are, I would use sp_readerrorlog to look at the error log.  I might even go so far as to create a stored procedure to encapsulate the functionality of sp_readerrorlog to produce output in whatever format you desire.

CREATE TABLE #ErrorLogAnalysis
(
  LogDate DATETIME,
  ProcessInfo NVARCHAR(255),
  [Text] NVARCHAR(MAX)
 );
INSERT INTO #ErrorLogAnalysis
EXEC sp_ReadErrorLog 0,1
SELECT *
FROM #ErrorLogAnalysis
WHERE [Text] NOT LIKE 'Login Succeeded%'
ORDER BY LogDate;
DROP TABLE #ErrorLogAnalysis


How to find out the models and prices for all the products (of any type) produced by maker B The database scheme consists of four tables:

Product (maker, model, type)
PC (code, model, speed, ram, hd, cd, price)
Laptop (code, model, speed, ram, hd, screen, price)
Printer (code, model, color, type, price)

The Product table contains data on the maker, model number, and type of product (PC, Laptop, or Printer). It is assumed that model numbers in the Product table are unique for all makers and product types.

Each personal computer in the PC table is unambiguously identified by a unique code, and is additionally characterized by its model (foreign key referring to the Product table), processor speed (in MHz) – speed column, RAM capacity (in Mb) - ram, hard disk drive capacity (in Gb) – hd, CD-ROM speed (e.g, 4x) - cd, and its price.

The Laptop table is similar to the PC table, except that instead of the CD-ROM speed, it contains the screen size (in inches) – screen.

For each printer model in the Printer table, its output type (y for color and n for monochrome) – color column, printing technology (Laser, Jet, or Matrix) – type, and price are specified.

Now the query I came up with:

SELECT PC.model, price FROM PC
INNER JOIN Product ON PC.model = Product.model
UNION
SELECT Laptop.model, price FROM Laptop
INNER JOIN Product ON Laptop.model = Product.model
UNION
SELECT printer.model, price FROM printer
INNER JOIN Product ON printer.model = Product.model
WHERE Product.maker='B'

The result should be:

model   price
1121    850.0000
1750    1200.0000

I am doing SQL exercise and not able to get the same result.

In a UNION/UNION ALL query, a WHERE clause filters a single sub-SELECT (the one immediately preceding it) rather than the entire UNIONed set – thus, each sub-select can have its own WHERE clause. (This is unlike e.g. ORDER BY, which you would be allowed to specify only once and it would apply to the combined set.)

So, if you want products by the same maker B from each of the three category tables, you need to repeat the filter for each sub-select:

SELECT PC.model, price FROM PC
INNER JOIN Product ON PC.model = Product.model
WHERE Product.maker='B'
UNION
SELECT Laptop.model, price FROM Laptop
INNER JOIN Product ON Laptop.model = Product.model
WHERE Product.maker='B'
UNION
SELECT printer.model, price FROM printer
INNER JOIN Product ON printer.model = Product.model
WHERE Product.maker='B'

That may look like redundant coding, which it probably is, and there are ways to eliminate the redundancy in this case – for instance by using a derived table:

SELECT
  derived.model, derived.price
FROM
  (
    SELECT model, price FROM PC
    UNION
    SELECT model, price FROM Laptop
    UNION
    SELECT model, price FROM printer
  ) AS derived
  INNER JOIN Product ON derived.model = Product.model
WHERE
  Product.maker='B'
;

But in SQL, the DRY principle does not apply as often as in other languages, meaning that repetition is not always bad in SQL and eliminating it is not always good. In theory, the two queries above should be equivalent. In practice, however, not every query optimiser might recognise that, and the second query might result in a less efficient execution plan.

In short, repeating a condition several times across a single query is not uncommon in SQL. Sometimes repetition becomes too much and, to help maintainability of your queries, you may want to employ views or stored routines. But in such cases, too, revisiting your schema often has an even better impact.

WITH CTE AS
(
SELECT model, price
from   PC
UNION ALL
SELECT model, price
from   Printer
UNION ALL
SELECT model, price
from   Laptop
)
SELECT distinct C.model, C.price
FROM   CTE as C INNER JOIN Product as P
ON     C.model = P.model
WHERE  P.maker = 'B';


Should I have a column for every data type or 1 column that stores everything I have a database table that currently looks like this:

PageField
-Id (Int, PK)
-FieldType (String) Can be Text, Decimal, Integer or Bit
-Value (This stores the value regardless of the FieldType, so it is not strongly typed)

Should I remove the Value column and replace it with 4 separate columns TextValue, DecimalValue, IntegerValue, and BitValue?

The data type would be set to what is relevant. This would mean in every row 3 of these 4 columns would be NULL.

This question (in various guises) crops up regularly. This type of "solution" is known as EAV (Entity-Attribute-Value) and is not a good idea. Take a look here or here for tips (or links to tips) on the problems it can cause and how to properly leverage the data types that your RDBMS offers.

Not using the correct data type is a sure fire way to confuse the
query optimiser and slow your system down. Joe Celko is a world renowned expert in all matters SQL and his opinions merit attention.

The fact that he uses the acronym "MUCK" (*) to describe this system should tell you all you need to know :-)

(*) Massively Unified Code Key

Search on the tag [eav] -- there are a lot of discussions of the evils of it.  One insidious evil comes when you need to sort numbers that are stored in a VARCHAR.


Why does SQL Server 2008 R2 insert an invalid datetime when using bulk insert? I want to insert rows with an optional datetime value (per row) using T-SQL BULK INSERT
on a SQL Server 2008 R2.

A table could look like this:

CREATE TABLE [dbo].[tbl_bulk_insert_datetime_issue] (
    [id] [int] NOT NULL,
    [description] [varchar](20) NOT NULL,
    [datetime] [datetime] NULL,
CONSTRAINT [pk_bulk_insert_datetime_issue] PRIMARY KEY CLUSTERED (
    [id] ASC
))

The insert batch:

BULK
INSERT [dbo].[tbl_bulk_insert_datetime_issue]
FROM 'C:\temp\bulkinsertsample.csv'
WITH
(
    FIELDTERMINATOR=';'
)

If I am to insert the following CSV content:

1;row01;
2;row02;20130401
3;row03;
4;row04;20130515

The table' content was parsed and inserted as expected:

id          description          datetime
----------- -------------------- -----------------------
1           row01                NULL
2           row02                2013-04-01 00:00:00.000
3           row03                NULL
4           row04                2013-05-15 00:00:00.000

But if the CSV file contains invalid data for the optional datetime

1;row01;
2;row02;20130401
3;row03;not_a_datetime
4;row04;20130515

SQL Server inserts the last valid datetime value instead of discarding the row with an error message:

id          description          datetime
----------- -------------------- -----------------------
1           row01                NULL
2           row02                2013-04-01 00:00:00.000
3           row03                2013-04-01 00:00:00.000
4           row04                2013-05-15 00:00:00.000

If I am to insert a row with an invalid datetime and no valid parsable datetime at all

1;row01;not_a_datetime

SQL Server inserts the default value of DATETIME:

id          description          datetime
----------- -------------------- -----------------------
1           row01                1900-01-01 00:00:00.000

Why does Sql Server insert the (for that specific row) invalid datetime when using bulk insert?
If I try to insert rubbish to an INT column the row will not be inserted by the bulk batch and an error will be thrown...

UPDATE:
I will share some information about the environment I used to reproduce the behaviour.

OS: Windows 7 Enterprise SP1 x64
regional settings: de-DE
Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (X64) Enterprise Edition
database collation: Latin1_General_100_CS_AS
file encoding: UCS-2 Little Endian (with Windows CRLF)

As hinted by Max Vernon the file encoding seems to be at fault. Setting the encoding to UTF-8 without BOM the bulk insert will throw the error I expected and discards the invalid row.

On SQL Server 2005, and SQL Server 2012, I did the following:

USE tempdb;
CREATE TABLE ImpTest
(
    ImpTestID INT NULL
    , ImpTestDate DATETIME NULL
);
GO
BULK INSERT ImpTest FROM 'C:\SQLServer\ImportTest.txt' WITH (FIELDTERMINATOR=',');
SELECT * FROM ImpTest;
DROP TABLE ImpTest;

With the following data:

1,
2,20130406
3,NOT_DATE
4,
5,20130409

On both servers, I received the following:

Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified
codepage) for row 3, column 2 (ImpTestDate).

Perhaps, as Aaron noted, this is a bug that needs to be addressed through http://connect.microsoft.com

I'm wondering about some of the details of your system, including locale settings, collations, physical format of the import file (is it from a Unix system? etc).

Think outside the box.  Ask yourself why you are holding the data in the first place.  What is the database for?  Well, holding data of course! But why, what is its use?  It’s to make data available to reports or to a front end.  In that sense, it doesn’t matter whether a date is held in a date or a character field.  I’ve seen solutions offered which say to import to a staging table and then run scripts to transfer and convert the data to the final destination.  Why?  Just import the data and then, if you must, create a view which does the conversion.  Make the view rather than the table available to reports or front end processes.  From a security point of view, that is probably best practise anyway.
Simple Example:

CREATE TABLE t_Input
    (
    StartDate VARCHAR(50),
    EndDate VARCHAR(50)
    )
GO
CREATE VIEW vw_Output
AS
    SELECT
        StartDate = CONVERT(datetime, StartDate, 101),
        EndDate = CONVERT(datetime, EndDate, 101)
    FROM
        t_Input

Then make the view available to the BI or Front end developers rather than the base table.


How to load textfiles into SQL Server Database? Sometimes I have to load csv-files to database. These files might be really huge (> 1TB).

I did not export them to this file and I cannot change anything about the format. The files just "fly in".
In most of the cases the files are seperated by "pipe".
Sometimes text is qualified. Within qualified text there might be "pipes", as well as there might be CR/NL-characters...
Sometimes text is not qualified. Within the text pipes are escaped with an backslash. A backslash might be escaped with another backslash...

The first thing I do to load these files is to get all content changed so that one line for the table is within on line of text.
The row delimiter must not remain within any textfield. I do this job with a commandline script using sed and/or tr.

The next step is to split the file in hany pices (< 50 MB). They would fit into any editor (more confort than less) and I can proceed them parallel.
(For a short time I need double the space of the original file in the filesystem).

Depending on the text formating I convert special characters within the text fields using commandline script using sed, if it gets more complex I use awk.
As the original file is split to hundreds of small files I can do this parallel.
To run (lets say 20) tasks parallel I use another commandline script.

The next step is to load these files into the database. For loading into MS-SQL-Server I use an SSIS-package with a for-each loop over all files
within a filesystem directory. For each file a table is created and the data of this file is loaded. If successfull the file is moved to a subfolder "success",
if an error occured the file is moved to a subfolder "error". So if something would stop the process, you could always restart at a defined point.

I build some copies of this package and filesystem-structures (load-folder_x, load-folder_x/success, load-folder_x/error) to start as many packages in parallel
as my I/O would accept.

When every file is in "success" I am almost done: At least data is inside the database. So I can loop through all the tables and do whatever I can do within a database.
All in all I need about 3 times the size of the original file for filesystem data and database file while the process is running.
A single process from file in filesystem to table in database would run with about a 20 to 30 MB/s. It does scale up to the maximum I/O of the server/disk-subsystem/network-connection.

My question is: How to improve this process?

The used tools are all "handmade", not documented and not so easy to understand for third parties.
errorhandling and logging is all "handmade" ... I guess there is a lot of potential improvement and I guess I am not alone with this kind of tasks.

Certainly you are person who knows a lot of technology tools etc. But I will just point here, that two things are probably worth of research:

Storage of the files on Linux filesystem. From my experience cames that even opening and listing directory with 100 000 or more files is a task which Microsoft cannot do in efficient way. Just using more efficient operating system here may give you at least some ergonomic gains.
Maybe you should research etl tools like Apatar. It works with many files by GUI, so in some cases you may have a visualization of your process, which probably makes it easier to automate or analyse.
It may be surprising in relation to this problem, but on the market there are very efficient data collection tools. Maybe thinking outside the box, and using clustered tools for system log acquisition, like Graylog log server, will be interesting choice. It has pipelining system, which takes data from network port, or through remote agents monitoring particular text files on remote system, and crunch it through various regex collectors, then has ability to divide that streams into output depending on given regex results. Output may be defined by you, and sent to another network device. Even on single machine acquisition of several hundreds of thousands records takes a seconds. It is very, very efficient. I believe at least part of your work may be automated here, for example it may test patterns and produce stream with wrong format entries ( or with good ones). And it gives you some basic statistical informations like dashboard with graphic representation of error types etc.

Through SQL Server Import and Export Wizard You can create simple packages that import and export data between many popular data formats including databases,spreadsheets,and text files. The import and export wizard also create the destination database and the tables into which the data is inserted.

for you ref further follow the instruction here


How to remove sysadmin from sa login in sql server I have a requirement wherein I need to remove sysadmin server role from sa login.
I created 1 more user and gave the sysadmin role to the user.
When I uncheck sysadmin from sa login it throws an error.
Cannot use the special principal sa error 15405.
Can anyone have any idea how to do this?

You cannot remove sysadmin from this login. The login with sid 0x01 (generally named "sa") is always sysadmin.
I assume that you have disabled sa in the first place, btw.
How about renaming sa to some other name?

You cannot drop the sa login and change the permissions of sa.But you can change its password, rename and disable it.


SUM Mixed With Case In Select Statement I am attempting to run this SELECT statement, but I keep getting error -

  Msg 130, Level 15, State 1, Line 9
  Cannot perform an aggregate function on an expression containing an aggregate or a subquery.

This is my syntax, how should this be re-written in order to succesfully execute?  I also add here make-up DDL to better illustrate question.

Declare @info TABLE (region varchar(50), p1 float, p2 float, p3 float, p4   float, p5 float, necombs float, d1 datetime)
INSERT INTO @info (region, p1, p2, p3, p4, p5, necombs, d1) VALUES
('west', 1.11, 2.22, 3.33, 4.44, 5.11, 22.00, '01/01/2015 08:00:00.000')
,('east', 2.11, 3.22, 3.33, 1.44, 2.11, 12.00, '01/03/2016 08:00:00.000')
,('north', 1.11, 2.22, 3.33, 4.44, 5.11, 22.00, '01/05/2015 08:00:00.000')

Select
region
,SUM(CASE WHEN YEAR(d1) = '2015' THEN ISNULL(p1, 0)+ISNULL(p2, 0)+ISNULL(p3, 0)+ISNULL(p4, 0)+ISNULL(p5, 0)/NULLIF(SUM(ISNULL(necombs, 0)), 0) ELSE 0 END) [Answer]
FROM @info
Group By Region

The accepted answer doesn't show the 2016 row, which I would expect to be 0 based on the initial SQL in the question.

Here's an alternative that would give the additional 2016 row:

 SELECT i.region,
        DATEPART(YEAR, i.d1) AS YearNumber,
        SUM(rd.Calc) / ISNULL(SUM(ISNULL(necombs, 0)), 0) Answer
 FROM @info i
 CROSS APPLY (VALUES(CASE WHEN DATEPART(YEAR, d1) = '2015' THEN ISNULL(p1, 0) + ISNULL(p2, 0) + ISNULL(p3, 0) + ISNULL(p4, 0) + ISNULL(p5, 0) ELSE 0 END)) AS rd(Calc)
 GROUP BY Region, DATEPART(YEAR, i.d1);

DECLARE @info TABLE
(
                    region  VARCHAR(50),
                    p1      FLOAT,
                    p2      FLOAT,
                    p3      FLOAT,
                    p4      FLOAT,
                    p5      FLOAT,
                    necombs FLOAT,
                    d1      DATETIME
)
INSERT INTO @info
(region,
 p1,
 p2,
 p3,
 p4,
 p5,
 necombs,
 d1
)
VALUES
('west',
 1.11,
 2.22,
 3.33,
 4.44,
 5.11,
 22.00,
 '01/01/2015 08:00:00.000'
),
('east',
 2.11,
 3.22,
 3.33,
 1.44,
 2.11,
 12.00,
 '01/03/2016 08:00:00.000'
),
('north',
 1.11,
 2.22,
 3.33,
 4.44,
 5.11,
 22.00,
 '01/05/2015 08:00:00.000'
);
WITH RawData
     AS (SELECT i.region,
                i.d1,
                DATEPART(year, d1) AS YearNumber,
                ISNULL(p1, 0) + ISNULL(p2, 0) + ISNULL(p3, 0) + ISNULL(p4, 0) + ISNULL(p5, 0) AS Calc,
                necombs
         FROM @info i)
     SELECT rd.region,
            rd.YearNumber,
            SUM(rd.Calc) / ISNULL(SUM(ISNULL(necombs, 0)), 0) Answer
     FROM RawData rd
     WHERE YearNumber = '2015'
     GROUP BY Region,
              YearNumber

Hope this helps.


Understanding SQL Server Cache I have a tricky question and I do not exactly know where to find relevant information to this.

I have SQL Server 2014 Enterprise installed on a database server. On this server there exist multiple databases, some belong to us and some to other companies.

My concern is with cache memory. I have a basic understanding of how SQL Server uses cache memory to retrieve data pages fast. You run a query, and usually, data pages get loaded to the cache and with repeatable calls, it gets results from this.

But... our application is highly used in usual working hours by our customers but at night there are always maintenance tasks going on. I am assuming (please tell me if I am wrong) that during the day, the cache gets loaded with useful data pages, but at night it is deleted because of the maintenance tasks, and so the next day, when customers start doing transactions, data pages have to be again loaded into the cache first, which takes time and slows a lot of things down.

So, can I somehow prove if that happens?

If yes, can I manage it? Such as somehow telling SQL Server to not load into cache?

I would be grateful for some specific articles discussing this topic. I could not find any, or better say, I do not know how to name this problem.

but at night it is deleted because of the maintenance task

I would not say deleted as more "overwritten" or "evicted". Maintenance may simply use the memory for other things. Not a big issue normally.

  and so a next day, when customers do transactions, data pages have to be again loaded
  to  Cache, which takes time and slows a lot of things down.

If that is really that brutal the case, you have either an edge case or a hardware problem.

Generally you can preseed the cache by running a number of "maintenance queries" in the early morning (to reload the data into the cache at 0600 in the morning for example) as part of maintenance. I.e. you end it by querying for the data you want loaded into the cache. Problem solved.

But generally this should not result in an EXTREME slowdown - yes, initial requests will be slower, but that should fix itself quite fast as the cache is going to fill up with every query. Is it possible that your experienced slowdown is not "cache" but "morning spike"? There are a lot of systems where at specific times (one of them the morning when people log in and check their work) the load is a lot higher than later during the day. In this case, you need to scale the hardware up to handle this. WOuld be among the first times I would have seen maintenance as a very significant issue.

sql has 4 steps for executing command and in last step it decided to how to get data. according to your scripts it my decide to get data from mdf or from cache

sql manage its cache by itself, it get data into its cache and whenever new data collected check its cache , if there is a free apace then it put it there if not, it Throw out the query which used The farthest time.

maintenance operations usually execute for update statistic , defrag index or rebuild it, shrink databases etc which it can  Throw out your cached data from cache


remove duplicate values in the same table My table looks like:

+----+---------+
| id | name    |
+----+---------+
|  1 | uska    |
|  2 | chinese |
|  3 | bakshi  |
|  4 | jj      |
|  5 | uska    |
+----+---------+

I want it to change it to

+----+---------+
| id | name    |
+----+---------+
|  1 | uska    |
|  2 | chinese |
|  3 | bakshi  |
|  4 | jj      |
+----+---------+

modify changes in the same table without switching to another table.

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

I tried in two ways like

delete from t where id<>(select id from t group by name);

Here I am getting an error as

  Table 't' is specified twice, both as a target for 'DELETE' and as a separate source for data"

delete t1.* from t t1,t t2 where 1.id<>t2.id and t1.name=t2.name;

2 duplicate rows are getting deleted instead of one.

Join table to itself:

DELETE z.*
  FROM table AS w
  JOIN table AS z  ON z.name = w.name
                  AND z.id > w.id
;

DELETE FROM MyTable
FROM         MyTable LEFT OUTER JOIN
    (SELECT     MIN(RowId) as RowId, Col1, Col2, Col3
     FROM         MyTable
     GROUP BY Col1, Col2, Col3) AS KeepRows ON
     MyTable.RowId = KeepRows.RowId
WHERE     (KeepRows.RowId IS NULL)


How to switch master server and warm standby (with streaming replication) in PostgreSQL? I would like to know what is the way to switch the role of 2 servers when using warm standby?

I have 2 servers : S_1 and S_2, and at a time T, S_1 is the master server and S_2 is a warm standby with streaming replication.

At a later moment, I would like to switch S_2 become the master server and S_1 the warm standby.

What do I have to do to switch roles ?

Keep in mind that currently you cannot demote a master to a standby.  Doing so will corrupt your database!

The basic documentation for doing this is at: http://www.postgresql.org/docs/9.1/static/warm-standby-failover.html

However the basic thing is you can't switch these other than:

Turn off primary
Promote secondary
Rebuild former primary as new secondary off the new primary.

PostgreSQL server can be promoted to master using below command:

pg_ctl promote -D

PostgreSQL server can be demoted to slave (post promotion using above command) by deleting/moving 'recovery.done' file @ /data dir. And restating the the server.


SQL Server 2008: how much RAM memory should SQL Server use in a 8GB RAM server? We have SQL Server 2008 and 8GB RAM in a customer, hosted in a Windows 2008 R2 SP1, and consider the following amount of free memory:

and consider the following running programs:

Scenario: Users use a website which saves data in this server in a SQL Server database. No more than 5 users.

Question: Considering scenario, that we have 8GB and we also have IIS hosted in this server (which I believe is a bad idea), in how much RAM should SQL Server use? Where can I configure the amount of RAM that SQL Server should really use without impacting Windows, IIS and any other running services?

  SQL Server version: Microsoft SQL Server 2008 (SP3) - 10.0.5500.0
  (X64)   Sep 21 2011 22:45:45   Copyright (c) 1988-2008 Microsoft
  Corporation  Standard Edition (64-bit) on Windows NT 6.1  (Build
  7601: Service Pack 1)

  Windows version: Microsoft Windows [Version 6.1.7601]

Question: Considering scenario, that we have 8GB and we also have IIS hosted in this server (which I believe is a bad idea), in how much RAM should SQL Server use?

The answer to the question "how much memory SQL Server would use" is, as much as it can. For that reason its always advisable to put appropriate value for max server memory so that buffer pool is restricted.

Please don't use task manager to get SQL Server memory utilization it wont give correct value in case when SQL Server service account has locked pages in memory(LPIM) privilege. To find how much RAM SQL Server is using please use below query

select
(physical_memory_in_use_kb/1024)Memory_usedby_Sqlserver_MB,
(locked_page_allocations_kb/1024 )Locked_pages_used_Sqlserver_MB,
(total_virtual_address_space_kb/1024 )Total_VAS_in_MB,
process_physical_memory_low,
process_virtual_memory_low
from sys. dm_os_process_memory

  Where can I configure the amount of RAM that SQL Server should really use without impacting Windows, IIS and any other running services?

You can do that in Sp_configure using below query.

sp_configure 'show advanced options',1
go
reconfigure
go
sp_configure 'max server memory (MB)' , xxx --value in MB which you need to set
go
reconfigure.

You can fix max server memory using the query but remember, SQL Server can still use memory outside buffer pool for third party DLL's and extended stored procs. This can lead SQL Server to use memory more that what is restricted in buffer pool

Please read this article. It would help you in setting starting value for max server memory. You should use below counters to set correct value for max server memory and monitor SQL Server memory usage.

SQLServer:Buffer Manager--Page Life Expectancy(PLE): PLE shows for how long page remain in buffer pool. The longer it stays the better it is. Its common misconception to take 300 as a baseline for PLE.   But it is not,I read it from Jonathan Kehayias book( troubleshooting SQL Server) that this value was baseline when SQL Server was of 2000 version and max RAM one could see was from 4-6 G. Now with 200G or RAM coming into picture this value is not correct. He also gave the formula( tentative) how to calculate it. Take the base counter value of 300 presented by most resources, and then determine a multiple of this value based on the configured buffer cache size, which is the 'max server memory' sp_ configure option in SQL Server, divided by 4 GB.   So, for a server with 32 GB allocated to the buffer pool, the PLE value should be at least (32/4)*300 = 2400. So far this has done good to me so I would recommend you to use it.

SQLServer:Buffer Manager--CheckpointPages/sec: Checkpoint pages /sec counter is important to know about memory pressure because if buffer cache is low then lots of new pages needs to be brought into and flushed out from buffer pool,  due to load checkpoint's work will increase and will start flushing out dirty pages very frequently. If this counter is high then your SQL Server buffer pool is not able to cope up with requests coming and we need to increase it by increasing buffer pool memory or by increasing physical RAM and then making adequate changes in Buffer pool size. Technically this value should be low if you are looking at line graph in perfmon this value should always touch base for stable system.

SQLServer:Buffer Manager--Freepages: This value should not be less you always want to see high value for it.

SQLServer:Memory Manager--Memory Grants Pending: If you see memory grants pending in buffer pool your server is facing SQL Server memory crunch and increasing memory would be a good idea. For memory grants please read this article

SQLServer:memory Manager--Target Server Memory: This is amount of memory SQL Server is trying to acquire.

SQLServer:memory Manager--Total Server memory This is current memory SQL Server has acquired.

Identify the Cache memory required for each database in your server and set Max. Server Memory higher than its cumulative value(not necessary to configure the value greater than twice of cumulative cache memory).

By doing this, you can allocate the memory needed for MSSQL server and free up the RAM unnecessarily occupied by the buffer cache.
Note: Max. Server Memory configuration parameter throttles only the buffer pool usage NOT the MSSQL server memory usage;

--space used by each database in the buffer pool

select
  count(*)*8/1024 AS 'Cached Size (MB)'
 ,case database_id
    when 32767 then 'ResourceDB'
    else db_name(database_id)
  end as 'Database'
from sys.dm_os_buffer_descriptors
group by db_name(database_id), database_id
order by 'Cached Size (MB)' desc;


Is there a sortable ('order by') alternative to CURRENT_TIMESTAMP I'm in the process of looking for a way to find a sortable alternative to a column that is populated with CURRENT_TIMESTAMP and I'm stumped at the moment.

I have a web based form with multiple fields including a field that is hidden on the form that writes the CURRENT_TIMESTAMP to the database when the form is submitted.

The challenge I'm facing is that I want to create reports from these forms (sorting by month, week, or year) and order them by the timestamp but based on what I've seen here, sorting is not possible.

Is there an alternative I can use to achieve the results I want?

I have no idea why you would want to ORDER BY CURRENT_TIMESTAMP. If the table has a column where you stored CURRENT_TIMESTAMP when the row was created, you just need to say:

ORDER BY that_column_name;

Or if you want newest first:

ORDER BY that_column_name DESC;

... in other words, forget how the column was populated.

Now, if the comments are accurate, and you're actually not concerned so much about ordering but rather about filtering, then perhaps what you want is:

-- create a DATE variable based on today:

DECLARE @s DATE = CURRENT_TIMESTAMP;

-- convert it to the first of the month
-- (you can do this in the step above,
-- just separating it for clarity):

SET @s = DATEADD(DAY, 1-DAY(@s), @s);

-- use >= and < in a WHERE clause, and order by that column
-- (however this is just a coincidence, order by and filtering
-- are completely unrelated in this case:

SELECT [?] FROM dbo.[?]
  WHERE [datetime_column] >= @s
  AND [datetime_column] < DATEADD(MONTH, 1, @s)
  ORDER BY [datetime_column];

Here is why you don't want to use BETWEEN, in case anyone sends you that way:

https://sqlblog.org/2011/10/19/what-do-between-and-the-devil-have-in-common

Here is why you want to use built-in date functions and a native DATE type and not convert to a string to trim time from a datetime variable:

http://www.sqlperformance.com/2012/09/t-sql-queries/what-is-the-most-efficient-way-to-trim-time-from-datetime
http://www.sqlperformance.com/2012/10/t-sql-queries/trim-time

And here is some general information about bad practices in date/range queries:

https://sqlblog.org/2009/10/16/bad-habits-to-kick-mis-handling-date-range-queries
https://www.sentryone.com/blog/aaronbertrand/backtobasics-dating-responsibly
https://www.mssqltips.com/sqlservertip/5206/sql-server-datetime-best-practices/

Yes you can do that using datepart function.
create view and add the 3 more column
as datepart(mm,CURRENT_TIMESTAMP) month,
datepart(wk,CURRENT_TIMESTAMP) week
datepart(yy,CURRENT_TIMESTAMP) year and apply the sort operation on view.

for more information you can refer to Datepart Function


Multiple Basic Availability Groups and Performance - SQL Server I'm migrating some of my company's SQL Server boxes to a new SQL 2016 setup. We're currently using database mirroring on SQL 2012. One of the instances I'm migrating currently has about 60 databases and despite the occasional hiccup, mirroring is working fine for us. And for various reasons this instance is running SQL Standard edition. As mirroring is being deprecated, I'm keen to try Basic Availability Groups.

However, I'm running into real performance issues putting together a lab with only 20 databases. During the initial setup, simply creating a new availability group will exhaust the 472 thread limit for Availability Groups (Max Threads - 40). On the primary server I'll see something like this:

time   active_workers idle_workers worker_limit worker_start_success
--------------------- ------------ ------------ --------------------
12:01  473            0            472          true
12:02  473            0            472          false
... multiple rows elided
12:05  472            451          472          false
12:06  22             16           472          true

Simply trying to view the dashboard for one of the availability groups will peg the CPU for several minutes. This isn't a super powerful VM, but VMWare tells me it has allocated 3014 MHz to it.

This is also without instrumenting any database operations or I/O on the database side.

Are there performance considerations I'm missing? At this point, I feel like Basic Availability Groups won't work for my situation, but I want to be sure I'm not overlooking something. I've leaned heavily on these articles/blog posts:

Monitoring SQL Server 2012 AlwaysOn Availability Groups Worker Thread Consumption
AlwaysOn Availability Groups – What Not to do when Adding Databases

Finally, this is the PowerShell script I've been using to automate the creation of availability groups. I may be doing something incorrect here:

Backup-SqlDatabase -Database $DatabaseName -BackupFile $DatabaseBackupFile -ServerInstance "vm-sqllab1"
Backup-SqlDatabase -Database $DatabaseName -BackupFile $LogBackupFile -ServerInstance "vm-sqllab1" -BackupAction 'Log'

Restore-SqlDatabase -Database $DatabaseName -BackupFile $DatabaseBackupFile -ServerInstance "vm-sqllab2" -NoRecovery
Restore-SqlDatabase -Database $DatabaseName -BackupFile $LogBackupFile -ServerInstance "vm-sqllab2" -RestoreAction 'Log' -NORECOVERY

$PrimaryReplica = New-SqlAvailabilityReplica -Name "vm-sqllab1" -EndpointUrl "TCP://vm-sqllab1.domain:5022" -FailoverMode "Manual" -AvailabilityMode "AsynchronousCommit" -AsTemplate -Version 13
$SecondaryReplica = New-SqlAvailabilityReplica -Name "vm-sqllab2" -EndpointUrl "TCP://vm-sqllab2.domain:5022" -FailoverMode "Manual" -AvailabilityMode "AsynchronousCommit" -AsTemplate -Version 13

$AgName = "vm-ag-" + $DatabaseName
New-SqlAvailabilityGroup -Path "SQLSERVER:\SQL\vm-sqllab1\Default\" -Name $AgName -AvailabilityReplica ($PrimaryReplica, $SecondaryReplica)  -BasicAvailabilityGroup -Database @($DatabaseName)
Join-SqlAvailabilityGroup -Path SQLSERVER:\SQL\vm-sqllab2\Default -Name $AgName
Add-SqlAvailabilityDatabase -Path SQLSERVER:\SQL\vm-sqllab2\Default\AvailabilityGroups\$AgName -Database $DatabaseName

There are two parts to this. First, make sure you install CU1. Notably it contains a fix for

Performance issues occur when using the sys.dm_hadr_availability_replica_states DMV on an AlwaysOn Availability Group that contains many databases in SQL Server 2016

The second part is to use T-SQL instead of PowerShell. When I do it this way, the availability groups get created much, much faster and I'm not seeing any of the CPU/thread issues I complained about above. This is the code I'm using to create the Availability Groups with direct seeding. I've adapted it from this blog post:

SQL Server 2016: Availability Groups, Direct Seeding, and You.

On the primary:

CREATE AVAILABILITY GROUP [vm-ag-TestDb21]
WITH (BASIC,
DB_FAILOVER = OFF,
DTC_SUPPORT = NONE)
FOR DATABASE [TestDb21]
REPLICA ON
N'vm-sqllab1' WITH (ENDPOINT_URL = N'TCP://vm-sqllab1.domain:5022',
    FAILOVER_MODE = MANUAL,
    AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,
    BACKUP_PRIORITY = 50,
    SECONDARY_ROLE(ALLOW_CONNECTIONS = NO),
    SEEDING_MODE = AUTOMATIC),
N'vm-sqllab2' WITH (ENDPOINT_URL = N'TCP://vm-sqllab2.domain:5022',
    FAILOVER_MODE = MANUAL,
    AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,
    BACKUP_PRIORITY = 50,
    SECONDARY_ROLE(ALLOW_CONNECTIONS = NO),
    SEEDING_MODE = AUTOMATIC)

GO

And on the replica:

ALTER AVAILABILITY GROUP [vm-ag-TestDb21] JOIN
GO
ALTER AVAILABILITY GROUP [vm-ag-TestDb21] GRANT CREATE ANY DATABASE
GO

Are you creating one Availability Groups per database? Why don't you create just one to control all databases in the same AG?
I believe the resource issue is related with Backup/Restore between both servers. Have you checked the network during this process?