SQL Server performance baseline benchmark In new SQL Server environment, I am trying to setup an performance baseline for some of the counters. Let me explain what I am doing.

I captured the performance counters through the performance monitor and get the data (in CSV format) for around a month's time and use it to find the average which will give an baseline for monthly performance.

Counters collected:

PhysicalDisk (_Total)\Avg. Disk sec/Read,
PhysicalDisk (_Total)\Avg. Disk sec/Write,
Processor (_Total)\% Processor Time,
Memory\Available MBytes,
SQL Server:Access Methods\Page Splits/sec,
SQL Server:Buffer Manager\Buffer cache hit ratio,
SQL Server:Buffer Manager\Page life expectancy,
SQL Server:SQL Statistics\SQL Compilations/sec,
SQL Server:SQL Statistics\SQL Re-Compilations/sec.

Can someone explain what I am doing to capture the performance baseline is correct or wrong?

There's a few different questions inside your post:

Q: Am I gathering the right counters?

I think you copied that Perfmon counter list from an older version of my Perfmon counter blog post. I've updated the counter list over time, so you may want to refresh it - or check with the folks where you got it, if someone in your team is keeping an offline copy.

Q: How should I gather them?

As I write in that post, I'd recommend capturing them every 15 seconds. (Every minute is fine too.) If you go any longer between captures, you can miss peak bursts of activity.

Q: Should I use the averages to build a baseline?

No - averages miss peak load, and include hours (or even days) of time when your SQL Server doesn't have any activity at all. You'll want to focus on periods of time where your end users are complaining about performance.


Select rows from table having two specific values I have a single table containing nothing but productID and colorID.  There are 10,000 rows in the table.

I need to be able to select all productID having colorID 2 and colorID 5.  For example:

productID colorID
1         1
2         9
3         2
4         2
3         6
3         5
1         5

Using this data set I am looking for productID 3, since it is the only productID that has colorID 2 AND colorID 5 (it also has colorID 6, but I don't care about that)

Using WHERE colorID=2 AND colorID=6 returns 0 rows.

Using WHERE colorID IN (2,6) returns values for products that do not have both colorID 2 and 6.

This seems like a simple thing to query, but I am finding myself up against a wall.  What's the best way to accomplish this in a reasonably efficient way?

You can also use a simple self-join. If there is no UNIQUE constraint on (ColorID, ProductID), then use SELECT DISTINCT:

SELECT productID
FROM tbl AS a
  JOIN tbl AS b
  USING (productID)
WHERE a.colorID = 2
  AND b.colorID = 6 ;


Best way to drop / recreate a table that has dependent views? Problem

PostgreSQL does not have logical column reordering, at least not in 9.2

If columns need to be added in the middle of a large table and order is important, the best way is to drop and recreate the table.  However, if there are external dependencies, such as views, this is not possible without DROP CASCADE.

If there are lots of views built on this table, it seems like a lot of work.  Is there any way to accomplish this without having to drop/recreate the views?

Hopeful

My hope is that you can do something like DROP CASCADE DEFERRED in a transaction.  So that the dependencies only drop if the original table doesn't exist at the end of the commit, but giving you the ability to drop the table, create the table, and commit without losing changes.

If you have the luxory of exclusive access a very simple method is to draw a backup with pg_dump, reorder columns as you chose in the CREATE TABLE scripts of the dump and restore the database. Other parts of the dump do not depend on column order. For instance, COPY statements list columns explicitly.

If you don't have this luxory, the whole operation is a pain. You need an exclusive lock on the table while dropping and recreating it - and all depending objects like views in its tail.
And concurrent queries on the table will still produce exceptions because they wait for the lock to go away, but then the table is gone (the table name has been resolved to a particular OID early in the process).

The Postgres Wiki on "Alter column position" has some more advice.

Decoupling logical column ordering from physical column ordering has been discussed repeatedly on pgsql-hackers, there is an entry in the ToDo Wiki by the name:

Allow column display reordering by recording a display, storage, and permanent id for every column?

And efforts are being made to maybe get this into Postgres 9.6:


What causes Transactional SQL Server Replication to need to be rebuilt/re-initialised In a company I've recently joined we have two SQL Server instances, connected via transactional replication.  I'm a developer and hadn't really come across SQL replication before so am a complete novice.  I'm looking at reducing the time it takes to deploy our products to the production environment and right now rebuilding replication is the worst offender.  A deployment often involves the standard SQL schema changes (new tables/indexes, modifications to existing tables/indexes) and we have bespoke SQL CLR code that is often needed.  At the moment if feels little like Russian roulette in that some changes can be applied without adversely affecting replication, whilst others (custom CLR code deploy) seems to require a rebuild (or is it the stored procs that must be dropped / recreated to access it?).

As a complete beginner with replication I was wondering if there is a resource or anyone knows a definitive list of changes that will force replication to need to be rebuilt?  Please don't ask why we've got replication, or SQL CLR code, neither should be needed but their removal is going to take a while and we need to get on top of the length of time deployments take first.  It would just be nice to understand before a release whether it was going to need to rebuild replication!

To answer the question, you would need to test how many rows your change will affect (an update to a column without a where clause, or adding a column will affect all rows). A reindex will not cause replication to be "rebuilt". Can you clarify what do you mean by "rebuilt" do you mean all rows are re-replicated? or do you mean you are forced to snapshot?

To know what is actually being replicated, you can use http://msdn.microsoft.com/en-us/library/ms186983.aspx


Is it possible to restore mongodb after db.dropDatabase() by replacing the contents of folder /data/db with older backup? In order to reset replication, I did drop the local mongo database using the following commands:

use local;
db.dropDatabase();

The mongo data directory(dbPath) is /data/db/

I took a backup of this folder before dropping the local database and reinstated the same after issuing db.dropDatabase(). After that restarted mongo service.

Is this the correct way and if not any chance of data loss pertaining to users, DB etc.?

Yes and no. Yes, you can do this and your DB is at same state what it was when you took that backup especially if you used journaling. This means of course that everything that you did (changes to the DB) after backup disappears.
No part is more like that if you do your backup this way,
you should use filesystem snapshots or you should other way prevent disk writing during the copy process.


Increase Oracle buffer cache gets slower So we're contemplating increasing our Oracle 11gR2 buffer cache.  They said they tried it in the past and things appeared to get worse - so they undid the change.  They, however, have no idea why things could have gotten worse.

Its possible they were preemptive and just didn't give it time considering it was full server reboot, but what are the situations where increasing the cache would yield worse results?  I've seen this page (situation B) but I can't find anybody else claiming that.  We're definitely doing more scattered reads than 'normal' probably given the kinds of queries we have to run.

In any system, if you make the part of the system that is not the bottleneck more efficient, there is the potential to make the system as a whole less efficient.  If, for example, your system's bottleneck is the CPU, making I/O more efficient by increasing the size of the buffer cache may end up causing many more threads to be waiting on the CPU which can make the system as a whole much slower as the operating system is now spending more time swapping between the competing thread, the CPU caches get overwhelmed, and you get the classic hockey stick graph of response time where once you get to the "knee", adding a bit more load adds a lot to the response time of the system.

If you have a system where I/O requests are highly uncorrelated, increasing the size of the cache could mean that Oracle will spend more time looking through the cache for a particular block before being forced to do a physical read after all.  That's probably relatively unlikely in an OLTP application but it's possible in a data warehousing type system where various reports are asking for blocks that no other report will want to reuse.

Of course, whether this is actually the problem that they encountered in the past is still an open question.  It is entirely possible that it was simply a matter of having a cold cache that needed to be warmed up or a perception problem where someone drew an incorrect conclusion from an earlier test or a poorly constructed test where, for example, someone inadvertently increased the size of the buffer cache at the expense of the PGA rather than at the expense of the operating system I/O cache.  I tend to share your distrust of human recollections of the past that aren't corroborated by some nice AWR reports so I'm not claiming that either of the above explanations are likely merely that they are potential explanations if the human recollections are correct.


Why would the user matter when running a query? I have a web page with a database lookup that contains multiple optional parameters.  When I select a couple of parameters and run the query, it returns in less than two seconds.  However, when another person logs in, and runs the same query with the same parameters on my machine, it takes 30+  seconds to come back.  Web browser doesn't matter, it behaves the exact same way in multiple different browsers for each user.  Both users have all the needed permissions on the database to run the query as they go through the same database connection using tcp/ip.  At this point, the only difference is who is actively logged in, which doesn't make any sense to me.  Does anyone have any ideas as to why the person would make a difference when everything else appears to be the exact same?  Thanks

There could be different reasons - I'll speculate some of them:

You could have row level security and have different rows returned for different callers
You could have resource governor enabled with different resource sizings for each user based on a login trigger
Perhaps the different users are on different machines and there are subtle differences in how the tests are run - for example, one user is always first and gets to compile the query not in the cache or there is a parallel plan that is parallel for one user and not the other due to load on the machine at the time
Maybe the queries are slightly different in text on the two machines and you are actually getting different query plans somehow for the two cases.

It usually helps to either do a profiler or XEvent trace to look at the exact runtimes and showplans at runtime to get insight into why one is slower.  On SQL 2016+, Query Store makes it easier (as is the case on SQL Azure), so if you have the option to try the repro on a later version it might help you find the answer.  Good luck!


Odd SQL Server Studio error, but not on SQL Server Express? I'm running a query that I got today from a previous question, and I just noticed an error that only happens when I run this query ON the server (RDC + MS Studio -- not express) vs on my machine which has MS Studio Express.

Here is the Error:

Msg 102, Level 15, State 1, Line 22
Incorrect syntax near '('.

It refers to this line in my script (full script here on DBA):

FROM sys.dm_db_index_physical_stats
    (
        db_id(@DB),  <---
        OBJECT_ID(@Table),

The odd thing is, that IF I run this off my local machine, using MS Studio Express AGAINST that same SQL server (dev box), it runs just fine and I get results.

HOWEVER if I remote into the SQL dev box, and paste the VERY SAME sql into the MS Studio (non-express) it throws that error msg 102... above.

I'm scratching my head why that is, my only thought is that the Studio Express is not validating it the same way as the non-studio client?

Here is the version info for the client on the SQL server:

Microsoft SQL Server Management Studio  9.00.4035.00
Microsoft Analysis Services Client Tools    2005.090.4035.00
Microsoft Data Access Components (MDAC) 2000.086.3959.00 (srv03_sp2_rtm.070216-1710)
Microsoft MSXML             2.6 3.0 4.0 6.0
Microsoft Internet Explorer         8.0.6001.18702
Microsoft .NET Framework            2.0.50727.3625
Operating System                5.2.3790

and my client:

Microsoft SQL Server Management Studio Express9.00.4035.00
Microsoft Data Access Components (MDAC) 6.1.7600.16385 (win7_rtm.090713-1255)
Microsoft MSXML             3.0 4.0 5.0 6.0
Microsoft Internet Explorer         8.0.7600.16385
Microsoft .NET Framework            2.0.50727.4963
Operating System                6.1.7600

Any ideas?

EDIT 1:

Per @gbn's suggestion I ran the version check on both (SELECT @@VERSION, @@SERVERNAME):

Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
Nov 24 2008 13:01:59
Copyright (c) 1988-2005 Microsoft Corporation Developer Edition
on Windows NT 5.2 (Build 3790: Service Pack 2)

(server name is same -- not posted)

EDIT 2:

Per @DaniSQL's suggestion I ran SELECT compatibility_level FROM sys.databases WHERE name = 'DATABASE_NAME' and the result was 80 (from both SMSS Express and while RDC'd to the box and connected with non Express SSMS)

The remote database has compatibility level 80, which doesn't work with db_id().  More info on a related StackOverflow question.

(Just dumping this into an answer from the question comments so it disappears from the Unanswered list of questions.)


Proc caching on read only db's/replicas I've been given the task of finding which stored procs are being called in our database.  To that end I started querying sys.dm_exec_cached_plans.  All is well and good, until I go over to our instance that has a read only copy of our DB and a read only secondary in our AG.  It looks to me like the DMV does not get updated when procs are executed.  Makes sense I suppose, but it there another way?  I can go the extended event route if I have to, but the DMVs are so much easier.

You're getting close, you're looking at the cached plans which says what has been executed. That doesn't tell you what's being executed right now, or yesterday, or in ten minutes.

Try this to see what's running right now.

SELECT  s.loginame, db_name(s.dbid) name, s.hostname,
 s.program_name,
 s.sql_handle, s.stmt_start, s.stmt_end,
 s.spid, CONVERT(smallint, s.waittype) waittype,
 s.lastwaittype, s.ecid, s.waittime , s.blocked,
 r.plan_handle,  r.statement_start_offset, r.statement_end_offset, r.query_plan_hash, r.start_time,
 q.plan_generation_num
FROM master..sysprocesses AS s WITH(NOLOCK)
LEFT OUTER JOIN sys.dm_exec_requests AS r WITH(NOLOCK)
   ON (s.spid = r.session_id)
LEFT OUTER JOIN sys.dm_exec_query_stats q
  ON q.plan_handle = r.plan_handle
  AND q.statement_start_offset = r.statement_start_offset
  AND q.statement_end_offset = r.statement_end_offset
WHERE (s.cmd<>'AWAITING COMMAND'
                AND s.cmd NOT LIKE '%BACKUP%'
                AND s.cmd NOT LIKE '%RESTORE%'
                AND s.cmd NOT LIKE 'FG MONITOR%'
                AND s.hostprocess > ''
                AND s.spid>50
                AND s.spid<>@@SPID)
AND lastwaittype NOT IN ('WAITFOR', 'SLEEP_TASK')
ORDER BY s.spid, s.ecid ASC

I would use SP_Whoisactive and log into a table for monitoring and seeing what's executing. Note, this is not the best way. This only captures what is running in real time when the query runs.

The next best way is to apply CDC / CDT / Trigger notifications on the databases / running SQL Profiler.


MySQL - How can I speed up a 50+GB fixed width text file import for a 1000+ column table? (Can't remove columns) I'm using MySQL 5.7.17 to import a ~50GB fixed width text file with over 1000 columns, but after 24 hours it still hasn't finished running.  I'm using MyISAM to get around the InnoDB column limit, and also because I thought it would be faster than InnoDB for importing (source here: https://dbahire.com/testing-the-fastest-way-to-import-a-table-into-mysql-and-some-interesting-5-7-performance-results/).

Everything I'm importing is from a fixed width text file, and I'm adding all of the text file data to CHAR rows in a ~1200 column table like this:

CREATE TABLE bigFile.BigFileTest (
  `var1` CHAR(3),
  `var2` CHAR(5),
  `var3` CHAR(12),
  ...
) ENGINE=MyISAM CHARSET = latin1;

LOAD DATA LOCAL INFILE
'C:\\path\\to\\file'
INTO TABLE bigFile.BigFileTest
(@row)
SET `var1` = TRIM(SUBSTR(@row,1,3)),
  `var2` = TRIM(SUBSTR(@row,4,8)),
  `var3` = TRIM(SUBSTR(@row,9,20))
  ...
;

I'd definitely prefer to not have a ~1200 column table, of course, but it's a requirement for the data I'm working with and I'm unable to change it.

Is there any way to speed up the import process?  I don't have much experience with database administration, so even basic changes you're all familiar with could be things I skipped.  I'm using MySQL Workbench with the defaults for everything except I specified ENGINE=MyISAM (as shown above).

Thanks for your help!

MyISAM is probably as fast as InnoDB for this action.

I suggest you minimize the work during the LOAD and plan on doing TRIM, etc, after the data is loaded.  That is, load the data into a staging table; then perform various conversions as you copy the data to the real table; then drop the staging table.

This 'staging' can also give you an easy way of splitting up the columns into tables.  Or consolidating the columns into a single JSON string, or something else.

If you provide more details on the data and the transformations and the 1000+, I can be more specific.

More

Oh! Sounds like Punched cards.  Or mag tape..  I would

Read the text into a single column declared as TEXT.
Probably I would also have another column: id INT UNSIGNED AUTO_INCREMENT NOT NULL.
Meanwhile, I would generate SUBSTR(t, 12, 23) clauses based on what the split up needs to be.
And decide how to build the schema.  Are these medical records?  Animal characteristics?  Quiz results?  What?  Census data?
Are some of the "columns" repeated a lot?  For example for census data, I might expect the first two columns to be "state" and have about 51 distinct 2-character values, then the next ~20 columns be the county, with about 3000 distinct values, etc.  If so, then that definitely suggests a structure (states contain counties contain cities ...)
After deciding on the several tables to spread the data across, build SELECT queries to pull the data out and put them into the other tables.

Continuing with the example:

CREATE TABLE Cities (
    id MEDIUMINT UNSIGNED AUTO_INCREMENT NOT NULL,
    state CHAR(2) NOT NULL,
    county VARCHAR(20) NOT NULL,
    city VARCHAR(20) NOT NULL,
    PRIMARY KEY(id),
    INDEX(state, city)
) ENGINE=InnoDB  CHARACTER SET ascii;

INSERT INTO Cities (state, county, city)
    SELECT DISTINCT
            SUBSTR(t, 1, 2) AS state,
            SUBSTR(t, 3, 20) AS county,
            SUBSTR(t, 23, 20) AS city
        FROM Raw;

After that, it gets trickier, because you want to use cities.id in whatever table comes next, rather than repeating the state+county+city again and again.

Get the idea?  Want to share the type of data you have?

Sure it will take hours, probably even days.  But the structured result will be better than a nearly useless regurgitation of the raw data.

Also, think about how you want to eventually query (SELECT) the data.


Configuring transactional replication with updatable subscription via SSMS 2012 Can I configure transactional replication with updatable subscription via SSMS 2012 (SQL Server 2012)? Is this removed from SSMS 2012 or I'm just missing something here?

updatable subscription is deprecated.

If you still want to implement it, you have to use T-SQL. You wont be able to do it using GUI.

See Updatable Subscriptions for Transactional Replication and Deprecated Features in SQL Server Replication


Conditional window function with a twist -- include at most X preceding rows but exclude some Asking for a friend who uses SQL Server Parallel Warehouse.

They™ has a table of weekly sales amounts, like so (please forgive improper column justification):

+-------+------------+
| week  |  amount    |
+-------+------------+
|    1  | 100.00     |
|    2  | 100.00     |
|    3  | 100.00     |
|    4  | 100.00     |
|    5  | 100000.00  |
|    6  | 100.00     |
|    7  | 50000.00   |
|    8  | 50000.00   |
|    9  | 50000.00   |
|   10  | 100.00     |
+-------+------------+

And also a list of "bad" weeks, e.g.

+------+
| week |
+------+
|    5 |
|    7 |
|    8 |
|    9 |
+------+

And They™ needs to select for each week, including "bad" weeks, the sum of sales for the four preceding not "bad" weeks, i.e. going as far back as possible, skipping "bad" week records, to add up at most four sales amounts. So the expected result would be:

+-------+------------+
| week  | sum_not_bad|
+-------+------------+
|    1  | null       |
|    2  | 100.00     |
|    3  | 200.00     |
|    4  | 300.00     |
|    5  | 400.00     |
|    6  | 400.00     |
|    7  | 400.00     |
|    8  | 400.00     |
|    9  | 400.00     |
|   10  | 400.00     |
+-------+------------+

I have a fiddle that has I think one step in the right direction but I can't figure out the next step(s).

Does anyone have insights?

SELECT *
FROM data as d
  OUTER APPLY
  (
      SELECT SUM(prev_amount) as sum_not_bad
      FROM
          (SELECT TOP(4) (p.amount)as prev_amount
           FROM data as p
               LEFT JOIN bad_weeks as b
                    ON p.week = b.week
           WHERE p.week < d.week
                AND b.week is null
           ORDER BY p.week DESC
          )a
  )oa

output

week        amount                                  sum_not_bad
1           100.00                                  NULL
2           100.00                                  100.00
3           100.00                                  200.00
4           100.00                                  300.00
5           100000.00                               400.00
6           100.00                                  400.00
7           50000.00                                400.00
8           50000.00                                400.00
9           50000.00                                400.00
10          100.00                                  400.00

dbfiddle


Fastest way to add a field to a large table I have a fairly large table (approx 400 million rows) and I need to add new column of type bit with NOT NULL constraint. My approach is to add the field without NOT NULL constraint, set the field to 0 and the alter the column to add the NOT NULL constraint.
This is a pretty slow process. Is there a quicker approach?

I am on SQL Server 2014 Enterprise Edition

As you are on Enterprise Edition and version >= 2012 then the information in Online non-NULL with values column add in SQL Server 2012 should apply.

Alter table yourtable
    add newcolumn bit not null default 0;

Should be metadata only and, unlike your proposed solution, will not have to write anything to all rows.


Shards error msg “can't use localhost … either use localhost or ipadresses” I'm trying to set up a a test environment on my pc including sharding. I've added 2 shardservers, a configsvr and a router instance. When i'm trying to add a shard I get following errormsg: "Can't use localhost as a shard since all shards need to communicate. Either use all shards and configdbs in localhost or all in actual IPs. host: localhost:27017 isLocalHost:1""

I searched online and I found a few topics about it but nowhere they explain how exactly and since I'm new to mongoDB it's quiet hard to understand what's going on. I looked here for example. But since I never used anything else than localhost I find it pretty weird.

This is how I set up:

2 shardsvr instances:

    mongod --shardsvr --replSet rs1 --port 27017 --dbpath D:\data\db
    mongod --shardsvr --replSet rs2 --port 27018 --dbpath D:\data2\db

1 configsvr instance:

    mongod --configsvr --replSet rs0 --port 27019 --dbpath D:\datacfg\db

I initialised the 3 replica sets (another question, do they need to be all different replicasets? or not?).

Then I added a mongos instance:

    mongos --configdb rs0/localhost:27019 --port 27020

And then when I'm trying to connect to mongos & addshards it goes wrong:

    mongo --port 27020

Since I'm never specifying a host I assume that he always uses localhost? no? I don't know how to check anyway. If you need more info tell me.

Thanks in advance

Yes, you cannot mix IP addresses and localhost. Those started mongod processes are now using your network device ip-address. If you don't know your machines ip-address, you can check address with ifconfig or ip address command.

Then if you connect mongos using that ip-address instead of localhost. Remember check that your firewall have all those ports open!


How to fix "TNS:listener does not currently know of SID given in connect descriptor" with DBVisualizer on Linux? I am trying to connect with Oracle database from a server having Oracle client installed (RHEL Linux Environment). I installed DB Visualizer on client machine. But getting listner error on hitting connect button.

I have tried TNSPING and connecting through SQL Plus and both are fine. What's the possible cause of the error in DBViz? How can I fix it?

DB Connectivity Detail:

TNS Ping

[root@bvqa-emmcas01 bin]# ./tnsping CMSQA
    Used TNSNAMES adapter to resolve the alias
    Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.160.189.78)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME=CMSQA)))
    OK (10 msec)

SQL Plus Connectivity

[root@bvqa-emmcas01 bin]# ./sqlplus temcsys/<password>@CMSQA
    SQL*Plus: Release 11.2.0.3.0 Production on Tue Mar 24 00:08:35 2015
    Release 11.2.0.3.0 Production on Tue Mar 24 00:08:35 2015
    Copyright (c) 1982, 2011, Oracle.  All rights reserved.
    Connected to:
    Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

tnsnames.ora

CMSQA=
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.160.189.78)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME=CMSQA)
    )
  )

Error in DB Visualzier

Long Message:
Listener refused the connection with the following error:
ORA-12505, TNS:listener does not currently know of SID given in connect descriptor

TNS Ping does not care about service or SID, all it checks is the availability of the listener, so this does not mean your connection string is correct. Connecting through SQL*Plus successfully however confirms it.

Your tnsnames.ora uses SERVICE_NAME in CONNECT_DATA, but your JDBC URL uses the SID syntax. SID and service name are different concepts, and their value can be different. In your case, you can connect with CMSQA provided as service name, but not as SID.

In this syntax, CMSQA means SID:

jdbc:oracle:thin:@10.160.189.78:1521:CMSQA

To use it as a service name, your connection string should be:

jdbc:oracle:thin:@//10.160.189.78:1521/CMSQA


MYSQL ERROR 2049 (HY000): Connection using old (pre-4.1.1) authentication protocol ref used (client option 'secure_auth' enabled) when I tried to restore all database dump which is in 5.0 version to 5.6 version, it got restored and after that when I tried to reconnect, am getting the following error

ERROR 2049 (HY000): Connection using old (pre-4.1.1) authentication protocol ref used (client option 'secure_auth' enabled)..

I have tried adding the following lines in My.ini and restarted the service,but the issue persist till.

skip-grant-tables
The following link says its a bug in MYSQL.

https://github.com/santisaez/powerstack/blob/master/packages/mysql/mysql-powerstack-secure_auth.patch

Do anyone have any fixes for this solution?

On the command line, use something like the following, if you have no choice...

mysql -uTheUseerNAme -pThePassword DbName -h HostName --skip-secure-auth

Hope this helps someone, as this was my problem connecting from a Linux


PostgreSQL: how can queries possibly be "immutable" / functional? I've read that PostgreSQL can have "functional" indexes.  For that you need the immutable keyword.

Seen that PostgreSQL is a CRUD DB I don't understand at all how it can possibly work in a functional way.

If there's an update or a delete to the DB, how can a past query guarantee that the result of the same query is always going to be the same?

I think you are getting confused with functional programming languages.

Anyway, a functional index is used to speed up access to data where queries that are accessing the data are using database functions.

By way of example, a query such as select blah from foo where sqrt(blah)=4 would have to perform the sqrt() operation for each value of blah. You could optimise this by pre-calculating all values of sqrt(blah) in a functional index: create index foo_func on foo (sqrt(blah));

This is where immutability comes in. A function is immutable if and only if the same input will always give the same output. Functional indexes can only use immutable functions. This guarantees that any queries for a given value and related functional index value will always return the same result.


Reusing the same connection to read uncommited transaction My question is similar to: how to spy on a transaction that is not yet committed in postgres for debugging
What I tried:

I start a pgbouncer instance, and set the max-db-connection to 1
From my node application, i connect to pg-bouncer
I BEGIN a transaction, and inserted a few rows, i.e: INSERT INTO USERS VALUES ('john')
I end the application without COMMIT
I connect to pgbouncer from another client (i.e: Datagrip)

Expected: I can continue from the uncommitted transaction, and SELECT * FROM USERS WHERE name = 'john', would retrieve the rows.
But what happens is that PgBouncer would close the connection ( closing because: unclean server (age=0s)) when a client connects, since it knows that there is an uncommited transaction. Is there a way to make this hack work with/without using PgBouncer?

You can only see uncommitted data modifications withing the transaction that performed them, so your client code would have to use the same transaction for everything. This sounds a lot like you are going to end up with long running transactions, which is a very bad idea in database, particularly in PostgreSQL.
I recommend that you approach this in a different way. Add a column committed to the table that you set to TRUE or FALSE (or something similar). Then you don't have to keep transactions open for a long time, and other transactions that choose to do so can simply SELECT “uncommitted data”.


modify a mysql "show" command? How do I write a query which takes the data from a "show" query and manipulate it?

In this specific instance, I need just one of the fields from "show slave status".

How do I do this?

  SELECT Slave_IO_State FROM (show slave status);

OR, how do I find out the underlying query for a "show" type command? I vaguely recall doing this in the past, but can't find any reference to it. Possibly an option in the mysql client? I tried logging the queries run, but that just showed the "show slave status".

I think you cannot use the result of SHOW commands as if they were tables. You can, however use the Performance Schema Replication Tables.

To get the Slave_IO_State, you have to take into account the following remarK.

  The Slave_IO_State and Slave_SQL_Running_State fields are not preserved. If needed, these values can be obtained from the process list by using the THREAD_ID column of the appropriate replication table and joining it with the ID column in the INFORMATION_SCHEMA PROCESSLIST table to select the STATE column of the latter table.


Setting up a Redundant Sql Server 2008 Configuration While Retaining High Performance and Minimal Downtime We have a situation where we are moving to a new hosting facility and are attempting to configure our new database environment.  However, none of us are formal DBA's so a lot of what we are doing is guessing and reading articles while attempting to make an informed decision.

Firstly, we are currently a small company who takes in several hundred thousand records (about 1/2 of them have image data) in a month.  We are growing quickly, and after a severe failure (a SAN array went down completely and we lost everything over a 12 hour period), we are moving to a new hosting facility with a better disaster recovery.

The new hosting facility will have our database on a 50 disk SAN.  Every minute, a snapshot is taken.  Should the SAN fail, the snapshot is loaded to another SAN, and the server is automatically brought up on the new SAN.  Downtime is a few minutes.

In addition to this, we want a redundant database setup.  We discussed having a Sql Server cluster using DFS on 2 separate SANs, but that violates PCI Compliance, which we must have.  Therefore, it is my opinion that a Cluster buys us no more redundancy than the hosting company's built in snapshot system in the event of a SAN failure.

The other option we are discussing is using mirroring.  However, the information we have read leads us to believe that mirroring with a witness will affect performance too greatly.  One option presented was to use "Safety Off" mirroring until we want to perform maintenance, then fire up the witness, take one server down at a time and perform the maintenance. Then, when complete, power down the witness.  This seems the best of both worlds, but also a headache.

So now the big question - With the snapshot system of our hosting company taking our downtime to mere minutes, what configuration is recommended to give use high availability as well as the best performance?

I would go with mirroring with a witness, do you want to do the work of a manual fail-over at 3AM when "socks hit the fan"?
Check out Paul Randal's MCM prep videos, they might help you out in your decision.


How to setup local database development process for small web team? Background

I am working on creating a new development process for a small web team of about 4 programmers and 4 designers, with the obvious potential to grow the team in the future.
Our product is a central application that powers client websites that we also design and host.

Previously, we all worked via FTP on a dev server, with a single dev database. It "worked"* for a while, but we are moving into a new direction, so it is time to mature our process.

We use Percona Server 5.5, but this should be database agnostic, with the idea of keeping costs low.

Goals:

I am looking into creating a continuous integration (CI) process for database development with the following in mind:

  Developers have local copy of data to run development code against
  Able to rollback database structure to a previous changeset
  Able to separate new feature schema changes vs schema design fix changes
  Able to modify database structure locally for testing

Initial Concept

I have sketched out a process below using SVN and LiquiBase, though it completely removes #4.

  create a 'development' branch from trunk
  central 'development' db server runs off the 'development' branch
  local developers are set up as slaves to the development branch (provides #1 above)
  liquibase changesets are committed regularly to the development branch, which execute a post-commit hook to update the central development database (which will trickle down to the local machines running as slaves to this development server) (liquibase provides #2 above)
  when features or schema fixes are ready to go to QA, DBA (me) will merge the appropriate changes from development branch into trunk. This act will create a sql script to apply to a staging database server.
  Staging server should reflect TRUNK, which should have the same structure as Production, plus changes that are in QA
  after executing the sql script on the staging server, do some QA on the changes.
  If all looks good, TAG the structure. This will generate the .sql script to be run in production manually by the DBA (for off-peak hours if required)

This process requires that all developers run off the same 'development' branch, meaning there is only one version of the database schema at any given time (not sure that I want this).

It also means that any changes to the schema cannot be tested out locally and could affect other developers if not done right. In our environment, developers might add new tables but rarely modify existing structure. As the DBA, design fixes are done by me. But the inability to test fixes locally is my biggest hangup of the process.

How can the above process be tweaked to allow local development, while still maintaining a relatively up-to-date copy of data (as provided by replication in my proposed process)? I do not require the data to be current up to even the last week.

* By 'worked', I mean it sufficed but was a PITA.

Managing environments

I think you definitely don't want to be forced into a single database version.  You've got enough developers that you will inevitably have multiple development work streams, and requirements to apply patches to the current production environment independent of development workstreams.

You can use Liquibase or a manual process to produce patch scripts to upgrade versions.  I suggest starting out with a manual process and using the schema comparison tool for QA on the patches.  Clean, automated, transparent synchronisation of a nontrivially complex database is a bit utopian.

Your central data model can be kept in whatever system takes your fancy.  I've used everything from tedious enterprise repository tools to create table scripts.  Create table scripts play nicely with ordinary source control tools such as subversion and not all repository tools do a good job of versioning.

Whatever you use as your master data model repository you need a fairly clean mechanism for deploying an environment from that model.  It should be structured so that rollouts to an environment are easy.  You also need a mechanism to patch from one released version to the next.

What I've done

I've done the following in the past when I was managing development environments.  It's not particularly high tech, but it's amenable to version control and automated builds, so it makes it easy to roll out an environment to a specific version, and maitaining a large number of environments is quite practical.

Maintain a central repository: This could be a set of database creation scripts held in a version control systems, or a repository model in a data modelling tool.  Take your pick.  This model should have a build mechanism that allows an environment to be rolled out from the scripts without a lot of manual intervention.

If you have a lot of reference data you will need a load mechanism for it.  Depending on how you want to do it, you could keep this in a database or in a set of files.  The advantage of files is that they can also be versioned and labelled from the same version control system as your code base.  A bunch of CSV files and bulk loader scripts in a source control repository can do this easily enough.

One option for deploying development environments is to take backups of the production database patched to the appropriate version and make them available for devs to restore into a development environment.

Make it easy to roll out: Like any CI build process, the database should be deployable from a single script.  Set it up so that database connections can be paramaterised, or the script is location independent and can just be run through the connection.

Patch scripts: You will need roll forward and probably roll back scripts from each released version.

Build test environments from the repository model: This ensures that development on environments that are out of sync with the repository gets caught in testing.

Test the deployment process: Automated patching scripts, however they are created should be testable.  Schema comparison tools are quite good for this, even if you dont't use them to generate the patch scripts.

Create a reference environment with the repository model build you tested against
Create a smoke test environment with either a backup of your production release or a build based on the current released version.
Run the patch script against the smoke test environment.
Use the schema comparison tool to compare the smoke test environment with the reference environment.  The patch script should result in the two databases being identical, so you can investigate any differences.

What I like about this process

This is a bit heavyweight, and was designed for deploying into fairly bureaucratic and opaque production environments.  However, it has the following strengths:

Developers can tinker where they need to.
Multiple branches and streams of development can be accommodated.
The deployment scripts themselves can be tested in a repeatable manner.  This is very helpful to shut down deployment bunfights, as the repeatability can be demonstrated.
The schema comparison tools provide QA on the deployment itself.
Testing is always done against what is expected to be released, and this will catch issues arising from environments being out of sync.
Deployment based on repository models and patch scripts means that uncontrolled rubbish doesn't get accidentally migrated from development environments into production.
A lot of the process can be automated, although it is often desirable to prepare and test the deployment patch scripts manually.
Environments are cheap and easy to deploy without having to jump through hoops.
Developers are forced to make a system that is amenable to a simple build and deployment process.
Developers are forced to learn basic database administration tasks, but test and production environments are insulated from noob mistakes.

How it addresses your requirements

Developers have local copy of data to run development code againstThe deployment scripts or DB images mean that they can set up an environment from any version that's available.
Able to rollback database structure to a previous changesetAgain, sorted by the deployment scripts.  Either through DDL or test database backup images created through a controlled process, developers can bring up an environment to any specific version that you have.
Able to separate new feature schema changes vs schema design fix changesPatches to a common version can be maintained in a separate fork in the svn tree.  If database backups are used as the reference environments they need to be stored somewhere with the same folder structure as the branching of the source control projects.
Able to modify database structure locally for testingThe simple deployment process allows devs to tinker, and easily restore an environment to a local state, or bring up a reference environment to do comparisons and make change sets against.


DB_OWNER permissions for user I would like to know if db_owner can change the recovery model of database.

Using TSQL-   alter database db set recovery simple

Well , its simple from the word itself db_owner:

  Members of the db_owner fixed database role can perform all
  configuration and maintenance activities on the database, and can also
  drop the database.

So yes , user with db_owner role can change the recovery model.

Also, for complete understanding please read Database-Level Roles


Why doesn't postgresql COPY command work with files in mapped drives I have a problem copying CSV files from a network shared drive into postgreSQL, at present the fix is to copy the files to a local drive first, but it's another process in a chain which is becoming too messy.

If I run the following command

  COPY test_schema.test FROM 'X:\_Postgres DBs\Testing\test_file.csv'
  CSV HEADER;

I get:

  ERROR:  could not open file "X:\_Postgres DBs\Testing\test_file.csv"
  for reading: No such file or directory

ok, so postgreSQL Server doesn't understand windows mapped drives, fine, but if I run:

COPY test_schema.test FROM '\\ws7.domain.com\share-name\_Postgres
DBs\Testing\test_file2.csv' CSV HEADER;

I get:

  ERROR:  could not open file "\\ws7.domain.com\share-name\_Postgres
  DBs\Testing\test_file2.csv" for reading: Permission denied

the file test_file2.csv can be opened locally on the same server with eg notepad/notepad++/wordpad so it doesn't look like there are any file attributes such as 'file in use'

Anyone have any tips how to use COPY with shared network drives?
(Our DB UI is coded in Delphi so I'm looking for ways to do with with postgresql scripts I can call from Delphi otherwise I'll have to write some fiddly Delphi Firedac batchcopy method.)

COPY runs as the PostgreSQL server, which usually runs as NETWORKSERVICE on Windows. This is a local service account that does not have access to your user account's network login credentials for shared drives.

Use the psql command \copy, which reads the file from the client application, running under your user ID and with access to your shared drives, then streams it over the client/server network connection to the postgres server.

I think PgAdmin-III has a similar CSV import command that uses COPY ... FROM STDIN like psql's \copy does, too.

If you're on a Windows AD Domain and you really need PostgreSQL to have direct access to shares, you can run the PostgreSQL server under a domain account instead. But that's usually unnecessary.


Calculate change in value window function when the identifier has dropped of the current period I was wondering how can i got about finding the change in value from one period to another but in my scenario the identifier has dropped off the current period.

I'm think this could be done with a cte and a select distinct id but i'm not sure how to implement.

Some demo date below

CREATE TABLE basic_pays (
    employee_id int,
    fiscal_year INT,
    salary DECIMAL(10 , 2 ),
    PRIMARY KEY (employee_id, fiscal_year)
);

INSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(1,2017,24000);
INSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(2,2017,17000);
INSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(1,2018,25920);

SELECT
    employee_id,
    fiscal_year,
    salary,
    salary - LAG(salary)
        OVER (PARTITION BY employee_id ORDER BY fiscal_year) salary_change
FROM
    basic_pays
GROUP BY
    employee_id, fiscal_year, salary
ORDER BY fiscal_year, employee_id

DROP TABLE basic_pays

The expected output would be

| employee_id | fiscal_year | salary   | salary_change |
|-------------|-------------|----------|---------------|
| 1           | 2017        | 24000.00 | NULL          |
| 2           | 2017        | 17000.00 | NULL          |
| 1           | 2018        | 25920.00 | 1920.00       |
| 2           | 2018        | 0.00     | -17000.00     |

You could do it like this, performance wise this could hurt on bigger datasets.

;WITH CTE
AS
(
SELECT DISTINCT employee_id,a.fiscal_year
from basic_pays
CROSS APPLY
(
SELECT distinct fiscal_year
from basic_pays
) as a)

SELECT   c.employee_id,
    c.fiscal_year,
    CASE WHEN salary IS NULL then 0.00 else salary END as salary,
    CASE WHEN salary IS NULL then 0.00 else salary END - LAG(salary)
        OVER (PARTITION BY c.employee_id ORDER BY c.fiscal_year) salary_change
FROM
    CTE c
LEFT JOIN basic_pays b
ON C.employee_id = b.employee_id
AND c.fiscal_year= b.fiscal_year;

Result

employee_id fiscal_year    salary       salary_change
1             2017        24000.00      NULL
1             2018        25920.00      1920.00
2             2017        17000.00      NULL
2             2018        NULL          -17000.00


Connection to Oracle DB 19c fails when listener is set to the host IP address I am new to Oracle DB and need help in solving the following issue.
I am using sqlplus (SQL*Plus: Version 21.1.0.0.0) to connect to the Oracle DB 19c.
Statement: I am able to connect to the Oracle DB from the host (192.168.0.109), VM running on top of the host (192.168.0.108), and also from other system within the same LAN (192.168.0.106) when I change the IP to 0.0.0.0 from 'localhost' in the listener.ora file as below:
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 0.0.0.0)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

Issue faced: Cannot connect from the VM (192.168.0.108) or from the other systems within the LAN (192.168.0.106) when I change the IP to the host IP (192.168.0.109) in the listener.ora file. Please find the modified listener.ora below:
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.109)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

Error Details: The error reported by sqlplus tool is as follows:
ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

Now, let me share the content of the tnsnames.ora file too (if required):
LISTENER_POLA =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))

ORACLR_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
    (CONNECT_DATA =
      (SID = CLRExtProc)
      (PRESENTATION = RO)
    )
  )

POLA =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = pola)
    )
  )

Some more details:

The host on which the Oracle DB is residing (192.168.0.109) is reachable from all the other systems including the VM
telnet 192.168.0.109 1521 from all the systems is showing it's connected.
The VM is running on Oracle Virtual Box 6.1 and is configured in Bridge mode.
The Oracle DB 19c and the VM is running on top of Windows Server 2016.

Query:
What configuration do I need to do so that sqlplus can connect to the Oracle DB from any system within the same LAN when the listener.ora file is configured with the IP address of the host system (192.168.0.109)?
Output of LSNRCTL status:
LSNRCTL for 64-bit Windows: Version 19.0.0.0.0 - Production on 16-MAR-2021 23:18:31

Copyright (c) 1991, 2019, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.0.109)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for 64-bit Windows: Version 19.0.0.0.0 - Production
Start Date                16-MAR-2021 23:16:07
Uptime                    0 days 0 hr. 2 min. 23 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   C:\Polarbear\WINDOWS.X64_193000_db_home\network\admin\listener.ora
Listener Log File         C:\Polarbear\diag\tnslsnr\WIN-4SO5O8T8UJ6\listener\alert\log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.0.109)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1521ipc)))
Services Summary...
Service "CLRExtProc" has 1 instance(s).
  Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

Output of the command sqlplus / as sysdba:
SQL*Plus: Release 19.0.0.0.0 - Production on Wed Mar 17 00:00:29 2021
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> select count(*) from dba_tables;

  COUNT(*)
----------
      2182

Maybe add something like this to your listener.ora:
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = POLA)
      (ORACLE_HOME= C:\Polarbear\WINDOWS.X64_193000_db_home)
      (SID_NAME = POLA)
    )
  )

You might run this query to verify your global name:
select global_name from global_name;

SID should be
select INSTANCE_NAME from v$instance;

Bobby


Problem with Czech collation Czech_CI_AI in MS SQL Server 2008 R2 I have the following issue with accent insensitive search in Czech language. My database is set to Czech_CI_AI collation. For some diacritics system works correctly (ie. I), but for some does not (ie. R). In my database there are several 'DVORA*' records:

SELECT contact_name
FROM   CONTCTSM1
WHERE  CONTACT_NAME LIKE 'DVO%'

Results:

  DVOŘÁČKOVÁ,
    IVETA DVOŘÁČKOVÁ,
    JIŘINA DVOŘÁK

R and Ř are different letters, as opposed to modified base letters(?) like German umlauts with ö vs o

From Czech language on Wikipedia (my bold)

  The acute accent (čárka) letters (Á, É, Í, Ó, Ú, Ý) and the kroužek letter Ů all indicate long vowels. They have the same alphabetical ordering as their non-diacritic counterparts. ...The háček (ˇ) indicates historical palatalization of the base letter. The letters Č, Ř, Š, and Ž currently represent postalveolar consonants and are ordered behind their corresponding base letters; while Ď, Ň, Ť represent palatal consonants and have the same alphabetical ordering as their non-diacritic counterparts.

A test with Ö and O in Swedish (different letters) and German (modified)

SELECT 'Finnish_Swedish_100_CI_AI'
          WHERE N'Ö' COLLATE Finnish_Swedish_100_CI_AI = N'O'
UNION ALL
SELECT 'Latin1_General_CI_AI'
          WHERE N'Ö' COLLATE Latin1_General_CI_AI = N'O'


Order by of records of a table in SQL Server I have the following tables:

Customer (ID, Name, CountryID)

Country (ID, Name, Code)

From the web application, user selects the number of Customers and the Countries then based on these parameters the system shows the result.

For example, user selects the number of customers as 2 and countries as US and UK then the system must show the result as follow:

CustomerID    CustomerName    Country
--------------------------------------
1             A               US
2             B               US
3             C               UK
4             D               UK
.
.
.

How can we achieve the above result?

Test

WITH
cte1 AS ( SELECT 'US' country, 10 cnt
          UNION ALL
          SELECT 'UK', 2
          -- UNION ALL
          -- ...
        ),
cte2 AS ( SELECT customer.*,
                 country.name country,
                 ROW_NUMBER() OVER (PARTITION BY country.name
                                    ORDER BY customer.id) rn
          FROM customer
          JOIN country on customer.country_id = country.id
        )
SELECT cte2.id CustomerID, cte2.name CustomerName, cte2.Country
FROM cte2
JOIN cte1 ON cte1.country = cte2.country
WHERE cte2.rn <= cte1.cnt


Warning message: invalid value for parameter "default_tablespace" in postgres I create a new tablespace using the following script:
CREATE SCHEMA semiarido;

CREATE ROLE semiarido PASSWORD 'senha';
GRANT CREATE ON SCHEMA semiarido TO semiarido;
GRANT USAGE ON SCHEMA semiarido TO semiarido;
alter role semiarido set default_tablespace = semiarido;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA semiarido TO semiarido;
ALTER ROLE "semiarido" WITH LOGIN;

When I connect to the database using psql or a python script, I get the following warning:
WARNING:  invalid value for parameter "default_tablespace": "semiarido"
DETAIL:  Tablespace "semiarido" does not exist.
However, it works, that is, the user is connected to the correct database and see "semiarido" as the default database.
So what's the problem? Why I'm getting this warning?

It seems you're confusing schemas and tablespaces. A schema defines a namespace whereas a tablespace points to a path on disk where data files are stored.
SET default_tablespace = name refers to a tablespace that would have been created with CREATE TABLESPACE.
A default schema can be assigned to a user through search_path, see Schemas in the documentation.


Time Required For Query Execution? I am new to working with databases and am unaware of their capacity in general.
To be specific, I want to know much much time would a particular mysql query take. I tried searching on the internet but nowhere was I able to find an estimate neither a way to find an estimate.

So my question is if there are 900k rows containing id (Numbers from 100 000 to 900 000) in ascending order in a table. How long would it take to search a row with a particular id?

I figured that if I ask someone who has worked with databases at this scale the estimation would be known to that person.

The execution speed of a database server depends on a great many factors, for example

concurrent load, what else is happening - that is if the database is
busy doing something else your query may have to wait
CPU speed, if the basic computing power of the database is slow your query will be executed slower than it would be otherwise
available disk IO bandwidth, if
the data is not cached in RAM and has to be fetched from a disk the
speed of the disk will affect performance
structure of the data, is it indexed to allow the DB to find your row more quickly?
network speed, how long does it take for the query to arrive at the DB and for the result to return to you app?

However to seek a single row out of 800,000 rows on a single table assuming the presence of an index on the column "id" that is specifying the row of data you want, it is likely to take a tiny fraction of a second on modern hardware. In fact the network round trip to your database server is likely to take longer than the database operation.


how to cache a subset for cascading select queries in mysql Here is another database problem I stumbled upon.

I have a date-range partitioned MyIsam look-up table with 200M records and ~150 columns. On this Table I need to perform cascading SELECT-Statements to filter the data. Output:

filter 126M
filter 110M
filter 40M
filter 5M
filter 100k

Every single SELECT is highly complex with regex (=no index possible) and multiple comparisons, which is why I want them to query the least amount of rows possible.

There are about 500 unique filters and around 200 constant users. Every filter needs to be run for each user, in total around 100k combinations.

Big question: Is there a way for each subsequent SELECT statement to query only the previous subset?

Example: Filter #5 should only have to query the 5M rows out of query 4 to get those 100k results. At the moment it has to scan through all 200M records.

EDIT
current approach:
cache table

CREATE TABLE IF NOT EXISTS `cache` (
  `filter_id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `lookup_id` int(11) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

ALTER TABLE `cache`
 ADD PRIMARY KEY (`filter_id`,`user_id`);

This would contain the relation between individual data-rows from the lookup table and the filters. PLUS I'd be able to use the rrimary index to get all of the lookup_ids from the previous filter.

Query for subsequent filters:

SELECT SUM( column), COUNT(*)
    FROM cache c
    LEFT JOIN lookup_table l ON c.lookup_id= l.id
    WHERE
    c.filter_id = 1
    AND c. user_id= x
    AND l.regex_column = preg_rlike...

If filter 110M is exactly a subset of filter 126M, then appending more AND with WHERE would have done the job.

$sql1 = "SELECT ..... WHERE ...";
$sql2 =  $sql1 . "AND column-name  = ....";
$sql3 =  $sql2 . "AND column-name  = ....";

If that is complex to accomplish try to CREATE VIEW of the previous SELECT statement and the next SELECT statement should query from the view.


How to perform conditional insert based on row count? I'm using Postgres 9.3, and I need to prevent inserts into a table based on a count of specific rows already in the table. Here's the table:

                                      Table "public.team_joins"
     Column      |           Type           |                            Modifiers
-----------------+--------------------------+---------------------------------------------------------
 id              | integer                  | not null default nextval('team_joins_id_seq'::regclass)
 team_id         | integer                  | not null
Indexes:
    "team_joins_pkey" PRIMARY KEY, btree (id)
    "team_joins_team_id" btree (team_id)
Foreign-key constraints:
    "team_id_refs_teams_id" FOREIGN KEY (team_id) REFERENCES teams(id) DEFERRABLE INITIALLY DEFERRED

So, for example, if a team with id 3 only allows 20 players, and SELECT COUNT(*) FROM team_joins WHERE team_id = 3 is equal to 20, no players should be able to join team 3. What's the best way to handle this and avoid concurrency issues? Should I use a SERIALIZABLE transaction to insert, or can I just use a WHERE clause like this in the insert statement?

INSERT INTO team_joins (team_id)
VALUES (3)
WHERE (
  SELECT COUNT(*) FROM team_joins WHERE team_id = 3
) < 20;

Or is there a better option that I'm not considering?

Typically, you have a team table (or similar) with a unique team_id column.
Your FK constraint indicates as much: ... REFERENCES teams(id) - so I'll work with teams(id).
Then, to avoid complications (race conditions or deadlocks) under concurrent write load, it's typically simplest and cheapest to take a write lock on the parent row in team and then, in the same transaction, write the child row(s) in team_joins (INSERT / UPDATE / DELETE).
BEGIN;

SELECT * FROM teams WHERE id = 3 FOR UPDATE;  -- write lock

INSERT INTO team_joins (team_id)
SELECT 3                -- inserting single row
FROM   team_joins
WHERE  team_id = 3
HAVING count(*) < 20;

COMMIT;

Example for single row INSERT. To process a whole set at once, you need to do more; see below.
One might suspect a corner case problem in the SELECT. What if there is no row with team_id = 3, yet? Wouldn't the WHERE clause cancel the INSERT?
It wouldn't, because the HAVING clause makes this an aggregation over the whole set which always returns exactly one row (which is eliminated if there are 20 or more for the given team_id already) - exactly the behavior you want. The manual:

If a query contains aggregate function calls, but no GROUP BY clause,
grouping still occurs: the result is a single group row (or perhaps no
rows at all, if the single row is then eliminated by HAVING). The same
is true if it contains a HAVING clause, even without any aggregate
function calls or GROUP BY clause.

Bold emphasis mine.
The case where no parent row is found is no problem either. Your FK constraint enforces referential integrity anyway. If team_id is not in the parent table, the transaction dies with a foreign key violation either way.
All possibly competing write operations on team_joins have to follow the same protocol.
In the UPDATE case, if you change the team_id, you would lock the source and the target team.
Locks are released at the end of the transaction. Detailed explanation in this closely related answer:

How to atomically replace a subset of table data

In Postgres 9.4 or later, the new, weaker FOR NO KEY UPDATE may be preferable. Also does the job, less blocking, potentially cheaper. The manual:

Behaves similarly to FOR UPDATE, except that the lock acquired is
weaker: this lock will not block SELECT FOR KEY SHARE commands that
attempt to acquire a lock on the same rows. This lock mode is also
acquired by any UPDATE that does not acquire a FOR UPDATE lock.

Another incentive to consider upgrading ...
Insert multiple players of the same team
Usefully assuming you have a column player_id integer NOT NULL. Same locking as above, plus ...
Short syntax:
INSERT INTO team_joins (team_id, player_id)
SELECT 3, unnest('{5,7,66}'::int[])
FROM   team_joins
WHERE  team_id = 3
HAVING count(*) < (21 - 3);  -- 3 being the number of rows to insert

The set-returning function in the SELECT list does not comply with standard SQL, but it's perfectly valid in Postgres.
Just don't combine multiple set-returning functions in the SELECT list before Postgres 10, which finally fixed some unexpected behavior there.
Cleaner, more verbose, standard SQL doing the same:
INSERT INTO team_joins (team_id, player_id)
SELECT team_id, player_id
FROM  (
   SELECT 3 AS team_id
   FROM   team_joins
   WHERE  team_id = 3
   HAVING count(*) < (21 - 3)
   ) t
CROSS JOIN (
   VALUES (5), (7), (66)
   ) p(player_id);

That's all or nothing. Like in a Blackjack game: one too many and the whole INSERT is out.
Function
To round it off, all of this could conveniently be encapsulated in a VARIADIC PL/pgSQL function:
CREATE OR REPLACE FUNCTION f_add_players(team_id int, VARIADIC player_ids int[])
  RETURNS bool AS
$func$
BEGIN
   SELECT * FROM teams WHERE id = 3 FOR UPDATE;         -- lock team
-- SELECT * FROM teams WHERE id = 3 FOR NO KEY UPDATE;  -- in pg 9.4+

   INSERT INTO team_joins (team_id, player_id)
   SELECT $1, unnest($2)                                -- use $1, not team_id
   FROM   team_joins t
   WHERE  t.team_id = $1                                -- table-qualify to disambiguate
   HAVING count(*) < 21 - array_length($2, 1);
   -- HAVING count(*) < 21 - cardinality($2);           -- in pg 9.4+

   RETURN FOUND;                                        -- true if INSERT
END
$func$  LANGUAGE plpgsql;

About FOUND.
Call (note the simple syntax with a list of values):
SELECT f_add_players(3, 5, 7, 66);

Or, to pass an actual array - note the VARIADIC key word again:
SELECT f_add_players(3, VARIADIC '{5,7,66}');

Related:

How to use an array as argument to a VARIADIC function in PostgreSQL?
Pass multiple values in single parameter


Query to normalize table/combine row text I have a table (call it oldTable) with columns like so:

ID (int),Rank (int),TextLineNumber (int),SomeText (varchar)

The primarykey is multi-part: ID+Rank+TextLineNumber.

I'm trying to transform/join it into another table (call it newTable) with columns like so:

ID (int), Rank (int), CombinedText (varchar)

and the primary key would be ID+Rank.

ID and Rank on the new table are already populated, but I need a query that would update the CombinedText column of the newTable with the following considerations:

The Rank given on the new table may not exist on the old table, in which case it needs to pick the highest available rank from the old table that is not greater than the rank on the new table.
The CombinedText column is a string concatenation of the "SomeText" column from the old table, concatenated in order of "TextLineNumber" using the Rank found from the first consideration.

Here's some example data:

old-
http://i54.tinypic.com/jq0vmx.png

new-
http://i53.tinypic.com/dhfyn8.png

I'm using MSSql 2005 if that matters.
I currently do this using T-SQL and while loops, but it's become a serious performance bottle neck (taking about 1 minute for 10000 rows).

Edit: Expanded example data in CSV:
Old:

ID,Rank,LineNumber,SomeText
1,1,1,the qu
1,1,2,ick br
1,1,3,own
1,2,1,some te
1,2,2,xt
1,3,1,sample
2,7,1,jumped ov
2,7,2,er the
2,7,3,lazy
2,13,1,samp
2,13,2,le text
3,1,1,ABC
3,1,2,DEF
3,1,3,GHI
3,1,4,JKL
3,50,1,XYZ

New:

ID,Rank,CombinedText
1,2,some text
2,13,sample text
2,14,sample text
3,4,ABCDEFGHIJKL
3,5,ABCDEFGHIJKL
3,50,XYZ
3,55,XYZ

edit2:
Here's an example query that I found that does work but isn't fast enough (relying on multiple sub-queries):

update newtable set combinedtext =
coalesce ((select top 1 sometext from OldTable where OldTable.id=newtable.id and oldtable.rank=(select top 1 rank from oldtable where oldtable.id=newtable.id and oldtable.rank<=newtable.rank order by rank desc) and oldtable.linenumber=1),'') +
coalesce ((select top 1 sometext from OldTable where OldTable.id=newtable.id and oldtable.rank=(select top 1 rank from oldtable where oldtable.id=newtable.id and oldtable.rank<=newtable.rank order by rank desc) and oldtable.linenumber=2),'') +
coalesce ((select top 1 sometext from OldTable where OldTable.id=newtable.id and oldtable.rank=(select top 1 rank from oldtable where oldtable.id=newtable.id and oldtable.rank<=newtable.rank order by rank desc) and oldtable.linenumber=3),'') +
coalesce ((select top 1 sometext from OldTable where OldTable.id=newtable.id and oldtable.rank=(select top 1 rank from oldtable where oldtable.id=newtable.id and oldtable.rank<=newtable.rank order by rank desc) and oldtable.linenumber=4),'') +
coalesce ((select top 1 sometext from OldTable where OldTable.id=newtable.id and oldtable.rank=(select top 1 rank from oldtable where oldtable.id=newtable.id and oldtable.rank<=newtable.rank order by rank desc) and oldtable.linenumber=5),'')

It also assumes a max line number of 5 which may not be the case. I don't mind hard-coding the linenumbers in all the way to a max of 20 if that's what it takes, but ideally it would be able to account for them differently. Getting execution time under 20 seconds (the actual data) is the goal...

You can create a function that stings the values together using a cursor within the function, but that's about your only option.  You'll need to do row by row processing to make this happen.


How can we decrypt data knowing the symmetric key used for the encryption? In this article is explained how to decrypt a symmetric key. For example:

SELECT SK.name, SK.symmetric_key_id, SK.key_length, SK.algorithm_desc,
       KE.crypt_type_desc,
       COALESCE(C.name,AK.name,PSK.name) AS protector_name,
       KE.crypt_property AS encrypted_key,
       COALESCE(DECRYPTBYCERT(C.certificate_id,KE.crypt_property),
                DECRYPTBYASYMKEY(AK.asymmetric_key_id,KE.crypt_property)) AS decrypted_key
  FROM sys.key_encryptions AS KE
  JOIN sys.symmetric_keys AS SK
    ON KE.key_id = SK.symmetric_key_id
  LEFT JOIN sys.certificates AS C
    ON KE.thumbprint = C.thumbprint
  LEFT JOIN sys.asymmetric_keys AS AK
    ON KE.thumbprint = AK.thumbprint
  LEFT JOIN sys.symmetric_keys AS PSK
    ON KE.thumbprint = CAST(PSK.key_guid AS VARBINARY(50));

It can be tested using the following query:

--
CREATE MASTER KEY ENCRYPTION
BY PASSWORD = 'smGK_MasterKeyPassword@';

--
CREATE CERTIFICATE [CERT_V001]
WITH SUBJECT = 'User for protecting SM symetric keys.'

--
CREATE SYMMETRIC KEY [SK_SecurityUsers_V001]
WITH ALGORITHM = AES_256 ENCRYPTION
BY CERTIFICATE [CERT_V001]
GO

DECLARE @Email NVARCHAR(128) = 'sometest@google.com';
DECLARE @EmailEncrypted VARBINARY(256);

OPEN SYMMETRIC KEY [SK_SecurityUsers_V001] DECRYPTION
BY CERTIFICATE [CERT_V001];

SELECT @EmailEncrypted = ENCRYPTBYKEY(KEY_GUID('SK_SecurityUsers_V001'),@Email);
SELECT @EmailEncrypted;

CLOSE SYMMETRIC KEY [SK_SecurityUsers_V001];

OPEN SYMMETRIC KEY [SK_SecurityUsers_V001] DECRYPTION
BY CERTIFICATE [CERT_V001];

SELECT CONVERT(NVARCHAR(128), DECRYPTBYKEY(@EmailEncrypted));

CLOSE SYMMETRIC KEY [SK_SecurityUsers_V001];

--DROP SYMMETRIC KEY [SK_SecurityUsers_V001];
--DROP CERTIFICATE [CERT_V001];
--DROP MASTER KEY;

I am wondering, does this mean the data is not protected at all?

In the article is said, that:

  However, for password-protected and symmetric-key-protected keys this
  sadly does not work.

I guess this means I need to use one of these types of encryption in order to be sure the data is protected?

I am wondering, does this mean the data is not protected at all?

No, it's protected but as shown in the article you need to have access to all of the keys in order to decrypt the values. The article takes a bunch of liberties and glosses over quite a few things when it comes to the protection of the keys. Try doing this without being a sysadmin, knowing the DMK password, or using transparent decryption hierarchy. Could you still do it - most likely not.

The secrets in SQL Server are generally protected by levels of encryption - where one key encrypts the other which encrypts the other. If you can unwind all of those levels (which again the article doesn't even really talk about and just kind of glosses over) then absolutely you can decrypt the data. SQL Server doesn't use any homemade encryption, it's all industry standard algorithms which are publically documented. There is nothing stopping anyone from implementing something to reverse it if they had all the keys. This is why over provisioning permissions is a bad thing.

  the data must be encrypted in rest and I want to check if the above behavior can give a person who has access to the database, but do not know the database master key password, the ability to encrypt the data.

In this case, no they generally wouldn't be able to get to the decrypted data. There are, however, edge cases that won't stop someone with administrative access and a decent working knowledge of encryption from possibly getting the data.

It's all about "reasonable protection" wherein you've given a reasonable enough protection to the data. If you wanted it to be extremely secure then you'd need to use an HSM, which another team owns and audits on an extremely frequent basis. No one would have administrative access to the server, and no one would have sysadmin access to SQL Server. That's probably not going to fly - so "reasonable" protection should be sufficed.


TokuDB does not support the repair table command, just like InnoDB, so you can't repair the table in question. If you don't have a backup or slave to pull from the best you can do is select the rows from the readable portion of the table itself. I'd recommend using "select * from table_name where table_pk < ? into ;" and "select * from table_name where table_pk > ? into ;". You'll need to experiment with the ? for each statement, because once you read the damaged part of the file the server will crash.


Use Alias In Join Condition I want to use "MaxID" as part of my join condition, but it keeps throwing an error:

  ERROR:  syntax error at or near "["
  LINE 29:   AND t1.["MaxID"]=t2.id

This is my query:

with MaxIDs as
(
 select max(id) as "MaxID"
 ,elID
 from valsum
 group by elID
)
select
t1.elID
,es.eNum
,es.des
,es.d1
,case
  when es.st = 1 then 'Pink'
  when es.st = 2 then 'Red'
  when es.st = 3 then 'Blue'
  ELSE es.st::text
END AS st
,t1.price
from MaxIDs t1
INNER JOIN etime es
ON t1.elID=es.elID
join valsum t2
ON t1.elID=t2.elID
AND t1.MaxID=t2.id
WHERE CAST(es.d1 As Date) BETWEEN '01/01/2016' AND '12/31/2016'
AND es.st IN (3)
ORDER BY CAST(es.d1 As Date) DESC

I also tried to use the alias w/o the brackets, but the same error was thrown.  How can I join on this field?

With no brackets and no quotes this is the error:

AND t1.MaxID=t2.id

  ERROR:  column t1.maxid does not exist
  LINE 29:   AND t1.MaxID=t2.id

Use t1."MaxID" literally the same as in with or drop quotes in with.


Preventing PostgreSQL from starting on boot in Ubuntu I am using PostgreSQL 9.2 and it runs automatically when I start the system (Kubuntu).

How can I avoid this?

I need to start it manually, and I don't want it to start unattended because I need to look at the console.

Ubuntu or Debian can run multiple instances of PostgreSQL and provide a specific way to autostart/stop/start each cluster.

There should be a file named start.conf inside /etc/postgresql/9.2/main (or more generally /etc/postgresql/<version>/<clustername>) with these self-explanatory contents:

# Automatic startup configuration
# auto: automatically start/stop the cluster in the init script
# manual: do not start/stop in init scripts, but allow manual startup with
#         pg_ctlcluster
# disabled: do not allow manual startup with pg_ctlcluster (this can be easily
#           circumvented and is only meant to be a small protection for
#           accidents).

auto

If you replace auto by manual, you could start this PostgreSQL instance only when desired with the command:

sudo pg_ctlcluster 9.2 main start

As for looking at the console, what you should want instead is having this run in a terminal when you work with the database:

tail -f /var/log/postgresql/postgresql-9.2-main.log


Scaling beyond 16 concurrent connetions I've just adopted a Postgres 9.0 database running on RHEL 6.1 on a rather beefy 256 core, 2TB system with ok drive (Hardware RAID 50).

I've started benchmarking the system using pgbench so I can get an idea of what this thing can do.  The application spends most of its time reading data, so I used the simple 'select' bgbench tests with the following parameters against a data set initialized with a scaling factor of 300:

pgbench -S -j 8 -T 60

I recorded the TPS reported from pgbench using n+8 (n = 0 to 16) clients for 16 samples.  I produced the following chart from the results:

My question is, why the peak at 16 concurrent connections?  I ran pgbench from the same host and looked at IO, memory, CPU utilization, all the usual suspects and found nothing that stood out as being a cause.  For postgresql.conf I have the following settings:

max_connections = 1024
shared_buffers = 16G
temp_buffers = 2G
work_mem =128M
maint_work_mem = 1G

%iowait is ~3.8% w/ 16 connections, 8.2% w/ 32, and 8.9% w/ 48.

I recently saw that PG 9.2 has a new 'feature' (http://www.postgresql.org/about/featurematrix/detail/218/) labeled as 'Multi-core scalability for read-only workloads' but this seems like a red herring.  I can't help but think it a configuration issue.

TIA.

Before spending much more time on it, upgrade to 9.2. New versions aren't just released for kicks, they tend to contain significant performance, tuning, and scalability improvements. Quite a lot of work has been done on read- and write-scaling recently.

Lower max_connections. Even unused entries in max_connections have a cost. On your machine you'll probably want to max out somewhere around 300. If you need more concurrent connections, put a pgbouncer in front of PostgreSQL.

Watch out for the known issues with NUMA and transparent huge pages in the Linux kernel; there are some pretty pathalogical interactions with PostgreSQL. I don't have details immediately to hand, but it's been discussed several times on the mailing lists. Some of the people on the team I work with do a lot of work with big NUMA systems and find Linux's handling of huge pages and page migration to be areas that require tuning for acceptable Pg performance.

On 9.0 your shared_buffers might be a bit big; you may actually see better results by lowering it. You'll need to play around with this as it varies a bit based on kernel and hardware.

Finally, make sure you run pgbench from a separate host, or it'll create contention for the same resources that it's trying to benchmark.

Set effective_cache_size to around 1TB. It should be higher, but IIRC there are some limitations in the code for handling multi-terabyte quantities of RAM, at least in 9.

If that RAID 50 is comprised of spinning rust it's going to limit performance unless your DB fits in RAM - which it probably does at  scale factor of 300, but might not on your real DB.


How dangerous is granting the ALTER TABLE permission? Imagine the following scenario

CREATE DATABASE test

GO

USE test;

CREATE TABLE dbo.Customer
  (
     CustomerId   INT,
     Email        VARCHAR(100),
     SensitiveData VARCHAR(20)
  );

INSERT INTO dbo.Customer
VALUES     (1,'abc@foo.com','12346789');

At some point an ETL process is written that performs some activities in the test database.

CREATE USER etlUser WITHOUT LOGIN; /*For demo purposes*/

CREATE TABLE dbo.StagingTable
  (
     StagingTableId INT,
     SomeData       VARCHAR(100),
  )

GRANT UPDATE,INSERT,DELETE,SELECT,ALTER ON dbo.StagingTable TO etlUser;

DENY SELECT ON dbo.Customer TO etlUser;
DENY SELECT ON dbo.Customer (SensitiveData) TO etlUser; /*For good measure*/

The etlUser should not have permissions to the Customer table (and certainly not to the SensitiveData column) so these are explicitly denied above.

The ETL process truncates dbo.StagingTable so is given ALTER table permissions on that.

This is flagged during a security audit. How dangerous is this scenario?

Pretty dangerous...

In addition to the obvious permission to change the structure of StagingTable itself the ALTER TABLE permission allows them to create triggers on the table. So in this case through ownership chaining they are able to both see sensitive customer data (despite the explicit DENY permissions) and perform vandalism on this second table.

EXECUTE AS user='etlUser'

GO

CREATE OR ALTER TRIGGER TR ON dbo.StagingTable AFTER UPDATE AS
/*Exposure of sensitive data*/
SELECT * FROM dbo.Customer;

/*Vandalism*/
DELETE FROM dbo.Customer;

go

--Fire the trigger
UPDATE dbo.StagingTable SET SomeData = SomeData WHERE 1=0;

REVERT


OPENXML question for SQL Server I am brand new to OPENXML, and have 90 % of what I want to do working...stuck on this:

<ReportHost name="411.114.42.42">
  <HostProperties>
    <tag name="HOST_END">Wed Nov 11 20:48:43 2015</tag>
    <tag name="LastAuthenticatedResults">8675309</tag>
    <tag name="Credentialed_Scan">true</tag>
    <tag name="policy-used">AWT v6</tag>
    <tag name="ssh-auth-meth">MyPassword</tag>
    <tag name="ssh-login-used">MyLogin</tag>
    <tag name="local-checks-proto">ssh</tag>
    <tag name="host-ip">411.114.42.42</tag>
    <tag name="HOST_START">Wed Nov 11 20:00:01 2015</tag>
  </HostProperties>
</ReportHost>

My Desired results:

Wed Nov 11 20:48:43 2015
8675309
TRUE
AWT v6
MyPassword
MyLogin
ssh
411.114.42.42
Wed Nov 11 20:00:01 2015

I am able to get the name value ("Host_end", etc.), but not the content.

My query:

Select   *
from OPENXML (@hdoc,'/ReportHost/HostProperties/tag',1)
WITH (
    name varchar (500)
     )

I'd like to stick with OPENXML if at all possible, since I just spent the day learning it and getting to this final step.

Please let me know if I have provided enough info.

You need to query for nodetype 3 to get the element text. I have changed your xpath expression slightly so it just returns the node text

EXEC sp_xml_preparedocument @hdoc OUTPUT, @XmlDocument
Select   [text]
from OPENXML (@hdoc,'/ReportHost/HostProperties/tag/text()',3)


What is faster Asynchronous SAN Replication or Asynchronous AAG? If copying data from one datacenter to another, what would generally be the faster approach to propagate that data to the target datacenter, SAN Replication or AAG, i.e. block-level or transaction-level, assuming the network, bandwidth and all other factors are excellent and the same for either method? Both would be asynchronous.

Thanks!

Let me make one thing clear before we go any further.

  ... SAN Replication or AAG, i.e. block-level or transaction-level

AlwaysOn Availability Groups do not work at the transaction level. The unit of work is at the log block level. This is very different as we ship log blocks for all transactions, whether they are committed or not.

The most likely "fastest" shipping mechanism would be the SAN replication given the parameters set forth and given the current versions of SQL Server generally available. This is only taking into account the literal question of "what method makes my data get to the other end the in the least amount of time?".


Cannot remotely connect to SQL Server 2012 VM on Azure I have a SQL Server 2012 instance running on a virtual machine in Azure. I have tried to connect to it using the public virtual IP address; however, I get an error saying:

  The server was not found or not accessible

I have checked that the allow remote connection tickbox is checked, and have allowed the .exe through the firewall. I was wondering if it's because I need to add some endpoints to my VM instance through Azure?

basic things to check:

new firewall inbound rule inside the VM enabling sqlservr.exe. You can make it as restrictive as you want but start with allowing all inbound connections first to verify it works then lockdown one thing at a time
new firewall inbound rule inside the VM enabling sqlbrowser.exe. Needed if running named instance.
new endpoint for the Azure VM (via Azure portal) to enable SQL
Server port. Some users specify some arbitrary unused port for public
and SQL Server's default 1433 for private to reduce noise from port
scanners that roam the internet looking for common targets. There are pros/cons to this or using default for both. You can
decide what's appropriate or talk to your security folks for
guidance.
TCP/IP enabled for SQL Server, should be enabled by default but takes 30s
to verify via Configuration Manager
SQL Server enabled for remote access, yes another obvious one but never hurts to verify


Shrinking VARCHAR with MODIFY: data alignment and potential for data loss I'm using MODIFY to change (shrink) a number of VARCHAR fields of various lengths in various tables. In each case, the field is called ip and is a textual representation of an IPv4 or IPv6 address. To that end, I want to set the fields to VARCHAR(45), which permits an IPv6 address using IPv4 tunnelling. My syntax, for completeness:

MODIFY ip VARCHAR(45) NOT NULL default ''

My question is this: if a field has previously been set as VARCHAR(100) or VARCHAR(254) and has content (one IP address per line), is there likely to be data loss? My understanding of VARCHAR is that the data is stored at the start (leftmost) and changes in size take place at the end (rightmost), so MODIFY would make its changes from the end and leave the data intact, being at the start. As far as versions are concerned it's various versions of MySQL from 4.1 to current.

I'm pretty confident that I can shrink ip to VARCHAR(45) without zapping existing data. All that said, I'm still pretty new to this and would appreciate an additional pair of eyes.

Thank you in advance.

You should properly examine the data metrics
Lengths of ip values greater than 45
SELECT IFNULL(LENGTH(ip),'Total'),COUNT(1) rowcount FROM mytable
GROUP BY LENGTH(ip) HAVING LENGTH(ip) > 45 WITH ROLLUP;

Suggested data type for ip using PROCEDURE ANALYSE()
SELECT ip FROM mytable PROCEDURE ANALYSE();

Whatever SELECT ip FROM mytable PROCEDURE ANALYSE(); reports is what you should use instead of just picking VARCHAR(45).
GIVE IT A TRY !!!


Database Design: Can only sell to X customers per given timeframe (ex: 11am to noon) I'm attempting to implement a system that limits the amount of total orders the system can accept for for a given timeframe.

Scenario: a business can only fulfill 5 orders per hours. Once that timeframe is filled, no more orders can be accepted. This feature would reset daily.

High Level Design:
Business Table - holds business information, etc. (structure: id(INT), name(VARCHAR), address(VARCHAR))

Timeframe Table - holds available timeframes for the day. (structure: id(INT), start_time(TIME), end_time(TIME))

Business Timeframes Table - reference business with available timeframes. (structure: id(INT), business_id(INT), timeframe_id(INT)).

Having set this up, I can assign a business with available timeframes. To create a counter, I'm thinking that I should create another table, which references a business_id, timeframe_id, available,  filled, and possibly datetime/timestamp.

Since the counter would reset daily, and there is a possibility of implementing future orders (deliver for today vs tomorrow vs day after), what would be the best way to go about this design?

First, you have to figure out how you want to define the system: is it the order date that is constrained by your timeframes, or is it the delivery date?

The design seems overly complicated to me. You could just store the relevant timestamp with the orders and error out if there are more than five in one hour.


Resource Waits: Mathematical link between Wait Time and Average Waiter Count Just curious, per BOL

Wait Time (ms/sec) is the wait time in milliseconds for all tasks waiting for one or more resources in the category since last update.

Average Waiter Count is the number of tasks waiting.

Is it a coincidence that on my server Average Waiter Count is always Wait Time / 1000 rounded to nearest 10th?  Is there a link here that makes it so?  Am I going to be embarrassed when the answer is extremely logical but for some reason escaping me at the moment?

i.e here are sample values from the last 15 minutes for latch but other wait categories are the same (when any values exist) - I've noticed it for quite some time (much longer than last 15 mins) it just never piqued my interest enough to investigate until now.

Wait Time         Average Waiter Count
______________________________________
576               0.6
1403              1.4
1856              1.9
1233              1.2
1365              1.4

It just seems to me that since wait time is a cumulative count and waiters is an average the numbers shouldn't follow a ratio of each other as tightly as they appear.

I think a task is considered waiting if it is waiting for a whole second -- 1000ms.
So if wait time for 1 second is 2000ms that means that 2 tasks where waiting in that second.


How to load entire database into memory? I want to load my entire database into memory, but how can I do this? I have about 256 GB memory and my database is about 200 GB, so I can easily handle in memory.

When I do select count(*) from table1 sqlserver automatically load table into memory and after this I'm able to use table very fast, but I want to know how can I load my entire database into memory?

If I do select count(*) from on each table I'm able to work faster but is there any other way to load entire database to memory? I want to load the entire database by a single command, not select count(*) from table by table.

I want to load my entire database into memory, but how can I do this?

Before figuring out "how" to do something, it is often best to be clear on "why" that something should be done. So, why exactly do you want to load your entire database into memory? Memory is a finite resource, so it needs to be used efficiently / wisely.

  I have about 256 GB memory and my database is about 200 GB, so I can easily handle in memory

Ok, so you appear to have 56 GB of RAM beyond the size of the database. But here is what is not being considered outside of the data itself:

Unless your company goes out of business, databases grow, even if you archive off older data. 56 GB will soon not be enough, right?
Memory is needed by the OS (Windows plus the related services such as Scheduled Tasks, etc)
Memory is needed by 3rd party software (monitoring, anti-virus maybe, etc)
Memory is needed to run the database engine process (the MSSQLSERVER or InstanceName NT Service)
Memory is needed to run the SQL Server Agent process
Each Connection / Session requires some amount of memory
Memory is needed for queries (look at the "memory grant" of a query plan):

aggregate operations
computations
sorting
etc

When objects call other objects, SQL Server maintains a call stack of each parent context so that control can revert back to that parent context when the current context completes. This call stack is managed in memory.
Query Plan cache
DMV stats / info
Result sets (or at least the pointers to the position within each Result Set that each process is currently at)
etc
etc

Now, regarding that 200 GB of data, you should consider this: Do you use all 200 GB of that data? I highly doubt it. Loading data pages into memory and not referencing them is inefficient and wasteful. You are far better off loading pages on demand because the disk I/O used to get them and the memory taken up by them will be worth both of those resources since you aren't wasting them on pages that nobody wanted anyway.

  I want to load entire database by single command not SELECT * table by table.

No, there is no single command to do this.

Also, the SELECT COUNT(*) method doesn't even accomplish this goal. All it does is load, via a Scan, the smallest object it can find, which might be a NonClustered Index, or it might actually be the base table itself.  But it doesn't load:

any indexes into memory (if scanning the Table) or the Table itself into memory (if scanning an Index), nor any objects that weren't scanned for the COUNT(*) operation. So you would still need to find a way to force an Index Scan for all Non-Clustered Indexes and/or a Table/Clustered Index Scan if a NonClustered Index was used for the COUNT(*) operation. But that would be even more wasteful because not only are they not always being used, but loading them each time would work against your ability to use the index DMVs to determine which indexes are, in fact, not being used!
any off-row LOB data pages (and probably not Overflow pages). You would need to select any LOB columns separately, and any variable-length columns that have Overflow data.


Does Resource Governor help protect page life expectancy? I have several large tables that I need to copy into partitioned copies on a transactional database.

I am worried about the hit that this will cause to performance of my production applications.

In an attempt to mitigate this, I am looking into Resource Governor.  I am going to set my script to use a resource pool that is limited to a max of 5% CPU and Memory.

The only part about this that I am not sure of is the effect on PLE.

As I understand it, the select from the table will read the value in to the cache.  This will force out something else that was in the cache. (The basic concept of PLE.)  But will the "forced out" items be limited to the 5% I have specified?

Meaning after I have used the 5% of the memory, will it only force out cached items in the same 5%? (Leaving the other 95% unaffected.)

Another way of asking this is: Is the memory limit of 5% the same as saying a cache limit of 5%?

This MSDN page states the memory settings only affect query memory:

Resource Governor Resource Pool

  MIN_MEMORY_PERCENT and MAX_MEMORY_PERCENT

  These settings are the minimum and maximum amount of memory reserved for the resource pool that can not be shared with other resource pools. The memory referenced here is query execution grant memory, not buffer pool memory (for example, data and index pages). Setting a minimum memory value for a pool means that you are ensuring that the percentage of memory specified will be available for any requests that might run in this resource pool. This is an important differentiator compared to MIN_CPU_PERCENT, because in this case memory may remain in the given resource pool even when the pool does not have any requests in the workload groups belonging to this pool. Therefore it is crucial that you be very careful when using this setting, because this memory will be unavailable for use by any other pool, even when there are no active requests. Setting a maximum memory value for a pool means that when requests are running in this pool, they will never get more than this percentage of memory.

The above indicates that buffer pool is excluded from resource pool limitations.  Therefore, if you are intending on performing some actions that may cause the entire buffer pool to be emptied in order to run your process, you may want to ensure that process is ran during off-hours.

The following is an excerpt from the MSDN blog, SQL Server Page Life Expectancy:

  Page Life Expectancy (PLE) is the best indication of how volatile your Buffer Pool is (BP). It's a PerfMon counter, found in the SQL Server:Buffer Manager PerfMon object.  There is also the Buffer Node:Page Life Expectancy counter which should be considered for NUMA systems, using the same logic per node as one would on a non-NUMA system.

  Volatility is measured by taking the average "life" of a page within the Buffer Pool (in seconds). If a page is overwritten or aged out, it starts a whole new life.

  So if lots of pages are being overwritten with new data very often, the average PLE will be low, and our BP volatility will be high.

  Conversely if most pages in our BP remain there for a long time without being overwritten, the average PLE will be high, and the BP volatility will be low.

  So why do we care about BP volatility and PLE? What can knowing the BP volatility do for us? What is a "good" and "bad" PLE figure?

  PLE can be a measure of how much physical IO your SQL Server is doing. Hopefully I've got your attention, because physical IO is a major performance concern, both for reading and writing.

From questions in the comments here:

  What is "query memory" and how is it different from the cache?

Query memory is used for execution of queries. It's temporarily used for things like sorts, bitmap creates, hash tables, etc. Cache contains in-memory table data.

  It sounds like the answer to my question is that the PLE will not be protected by the Resource Governor at all. Does that sound right?

Correct. Page Life Expectancy is all about how long data pages stay in memory.


Reporting Table Design I'm writing a system that allows users to store values against each week in October and November and each day in December - starting from the first Monday. I'm holding the raw data entered by the users in a transactional set of tables (3NF I believe). Administrators will be pulling regular reports of that data. As a result, each time the user makes a save I want to extract the data into a denormalised MI table, to make the reporting faster, and help reduce locking on the transactional tables.

Bearing in mind that different years have different numbers of weeks in Oct and Nov, and different number of days in Dec (because we start from first Monday), what is a good design for the MI table? So, something like:

OctW1
OctW2
..
NovW4
NovW5
DecMon1
DecTues2
...
DecMon5
DecTue5

I've considered:

creating a separate table for each year (tblMI_2012, tblMI_2013 etc), so the schema for each year is only defined once, and all the fields will be populated on each row.
creating a column for each possible value (so 31 fields for all the December dates) and only populating them when saving values for the respective calendar year.

Which pattern is the best?

Also, should I be also saving the date with the value (effectively doubling the column count)

OctW1
OctW1Date
OctW2
OctW2Date
etc

Apologies if this is really obvious, my first foray into creating an reporting table.

I would do something like this:

period_start_on date
period_duration interval
position        int -- position of this row in the year,
                    -- e.g. 1 for first Monday of Oct,
                    -- 2 for 2nd Monday, etc.
values ...
primary key(period_start_on, period_duration)
check(position >= 1 and position < 40)

Then if you want December only, you use WHERE period_duration = '1d' and period_start_on >= '2012-12-01' AND period_start_on < '2013-01-01'. If you want to compare two years, you JOIN using position:

SELECT y1.*, y2.*
FROM   the_table y1
  JOIN the_table y2 USING (position)
WHERE y1.period_start_on >= '2011-01-01' AND y1.period_start_on < '2012-01-01'
  AND y2.period_start_on >= '2012-01-01' AND y2.period_start_on < '2013-01-01'