Why do we need to use proxies for syadmin job steps? I read somewhere that for non-TSQL SQL Server Agent jobs, the job runs under SQL Server Service Agent account when the job owner is the sysadmin.

I just don't get why we need to use proxies even the owner of the step is a member of sysadmin fixed server role.

Proxies allow non-sysadmins to manage and maintain SQL jobs

I had similar thoughts for the longest time. BUT we did finally find a valid business use for them. The short answer is, proxies allow you to delegate SQL Agent jobs to non-sysadmins so they can manage, change and/or execute those jobs using various hooks into powerful SQL features like SSIS, SSAS, command-line, etc.

For example, we are increasingly getting developers who are implementing data marts and ETL SQL jobs using SSIS and/or Analysis Services. But, as you might imagine, their coding prowess isn't always up to par. Our DBAs used to spend hours trouble-shooting the poor coding and ETL because they had the sysadmin rights to the SQL Agent jobs and the developers did not. And those SQL Agent ETL jobs can take forever to run. And if it breaks, you're running it again and again at the behest of the developers who can't do that.

Proxies change all that. With proxies enabled in SQL we can give the developers a credential that maps a the proxy to allow them to manage, monitor, and maintain those ETL/SSIS jobs. No more DBA interaction required.

Because their jobs are assigned with their proxy/credential account, they (the developers or non-sysadmins) can review those jobs, re-run them, change them etc.

For example, we associated their domain\user with a SQL Server Credential/Proxy as shown in Figure A and B. Figure B is the actual AD password of the user so you may need to Skype and share your screen so they can type in their password:

Figure A: Create a credential

Figure B: Associate the credential with an Active Directory non-sysadmin

Then, we created/assigned the credential to the proxy to the SQL job functions we wanted to delegate as shown in Figure C. We named our credential identically to the proxy--so don't be confused there.

Figure C: Associate the proxy with the credential for the job types you'd like to delegate.

Finally, we associated the proxy with the particular job. as shown in Figure D. Now, those developers have full control over those specific jobs and SQL Agent features within those jobs.

Figure D: Add/run the proxy to the jobs you'd like to delegate.

Note: If your organization has a mandatory AD password change every few months, you will have to share your screen and allow the user to change their password for their associated SQL credential.


Is ALTER TABLE ... DROP COLUMN really a metadata only operation? I've found several sources that state ALTER TABLE ... DROP COLUMN is a meta-data only operation.

Source

How can this be? Does the data during a DROP COLUMN not need to be purged from the underlying non-clustered indexes and clustered index / heap?

In addition, why do the Microsoft Docs imply that it is a fully logged operation?

  The modifications made to the table are logged and fully recoverable. Changes that affect all the rows in large tables, such as dropping a column or, on some editions of SQL Server, adding a NOT NULL column with a default value, can take a long time to complete and generate many log records. Run these ALTER TABLE statements with the same care as any INSERT, UPDATE, or DELETE statement that affects many rows.

As a secondary question: how does the engine keep track of dropped columns if the data isn't removed from the underlying pages?

There are certain circumstances where dropping a column can be a meta-data-only operation.  The column definitions for any given table are not included in each and every page where rows are stored, column definitions are only stored in the database metadata, including sys.sysrowsets, sys.sysrscols, etc.

When dropping a column that is not referenced by any other object, the storage engine simply marks the column definition as no longer present by deleting the pertinent details from various system tables.  The action of deleting the meta-data invalidates the procedure cache, necessitating a recompile whenever a  query subsequently references that table.  Since the recompile only returns columns that currently exist in the table, the column details for the dropped column are never even asked for; the storage engine skips the bytes stored in each page for that column, as if the column no longer exists.

When a subsequent DML operation occurs against the table, the pages that are affected are re-written without the data for the dropped column.  If you rebuild a clustered index or a heap, all the bytes for the dropped column are naturally not written back to the page on disk.  This effectively spreads the load of dropping the column over time, making it less noticeable.

There are circumstances where you cannot drop a column, such as when the column is included in an index, or when you've manually created a statistics object for the column.  I wrote a blog post showing the error that is presented when attempting to alter a column with a manually created statistics object.  The same semantics apply when dropping a column - if the column is referenced by any other object, it cannot simply be dropped.  The referencing object must be altered first, then the column can be dropped.

This is fairly easy to show by looking at the contents of the transaction log after dropping a column.  The code below creates a table with a single 8,000 long char column.  It adds a row, then drops it, and displays the contents of the transaction log applicable to the drop operation.  The log records show modifications to various system tables where the table and column definitions are stored.  If the column data was actually being deleted from the pages allocated to the table, you'd see log records recording the actual page data; there are no such records.

DROP TABLE IF EXISTS dbo.DropColumnTest;
GO
CREATE TABLE dbo.DropColumnTest
(
    rid int NOT NULL
        CONSTRAINT DropColumnTest_pkc
        PRIMARY KEY CLUSTERED
    , someCol varchar(8000) NOT NULL
);

INSERT INTO dbo.DropColumnTest (rid, someCol)
SELECT 1, REPLICATE('Z', 8000);
GO

DECLARE @startLSN nvarchar(25);

SELECT TOP(1) @startLSN = dl.[Current LSN]
FROM sys.fn_dblog(NULL, NULL) dl
ORDER BY dl.[Current LSN] DESC;

DECLARE @a int = CONVERT(varbinary(8), '0x' + CONVERT(varchar(10),      LEFT(@startLSN, 8), 0), 1)
      , @b int = CONVERT(varbinary(8), '0x' + CONVERT(varchar(10), SUBSTRING(@startLSN, 10, 8), 0), 1)
      , @c int = CONVERT(varbinary(8), '0x' + CONVERT(varchar(10),     RIGHT(@startLSN, 4), 0), 1);

SELECT @startLSN = CONVERT(varchar(8), @a, 1)
    + ':' + CONVERT(varchar(8), @b, 1)
    + ':' + CONVERT(varchar(8), @c, 1)

ALTER TABLE dbo.DropColumnTest DROP COLUMN someCol;

SELECT *
FROM sys.fn_dblog(@startLSN, NULL)

--modify an existing data row
SELECT TOP(1) @startLSN = dl.[Current LSN]
FROM sys.fn_dblog(NULL, NULL) dl
ORDER BY dl.[Current LSN] DESC;

SET @a = CONVERT(varbinary(8), '0x' + CONVERT(varchar(10),      LEFT(@startLSN, 8), 0), 1);
SET @b = CONVERT(varbinary(8), '0x' + CONVERT(varchar(10), SUBSTRING(@startLSN, 10, 8), 0), 1);
SET @c = CONVERT(varbinary(8), '0x' + CONVERT(varchar(10),     RIGHT(@startLSN, 4), 0), 1);

SELECT @startLSN = CONVERT(varchar(8), @a, 1)
    + ':' + CONVERT(varchar(8), @b, 1)
    + ':' + CONVERT(varchar(8), @c, 1)

UPDATE dbo.DropColumnTest SET rid = 2;

SELECT *
FROM sys.fn_dblog(@startLSN, NULL)

(The output is too big to show here, and dbfiddle.uk won't allow me to access fn_dblog)

The first set of output shows the log as a result of the DDL statement dropping the column.  The second set of output shows the log after running the DML statement where we update the rid column.  In the second result set, we see log records indicating a delete against dbo.DropColumnTest, followed by an insert into dbo.DropColumnTest.  Each Log Record Length is 8116, indicating the actual page was updated.

As you can see from the output of the fn_dblog command in the test above, the entire operation is fully logged.  This goes for simple recovery, as well as full recovery.  The terminology "fully logged" maybe misinterpreted as the data modification is not logged.  This is not what happens - the modification is logged, and can be fully rolled back.  The log is simply only recording the pages that were touched, and since none of the table's data-pages were logged by the DDL operation, both the DROP COLUMN, and any rollback that might occur will happen extremely quickly, regardless of the size of the table.

For science, the following code will dump the data pages for the table included in the code above, using DBCC PAGE, style "3".  Style "3" indicates we want the page header plus detailed per-row interpretation.  The code uses a cursor to display the details for every page in the table, so you may want to make sure you don't run this on a large table.

DBCC TRACEON(3604); --directs out from DBCC commands to the console, instead of the error log
DECLARE @dbid int = DB_ID();
DECLARE @fileid int;
DECLARE @pageid int;
DECLARE cur CURSOR LOCAL FORWARD_ONLY STATIC READ_ONLY
FOR
SELECT dpa.allocated_page_file_id
    , dpa.allocated_page_page_id
FROM sys.schemas s
    INNER JOIN sys.objects o ON o.schema_id = s.schema_id
CROSS APPLY sys.dm_db_database_page_allocations(DB_ID(), o.object_id, NULL, NULL, 'DETAILED') dpa
WHERE o.name = N'DropColumnTest'
    AND s.name = N'dbo'
    AND dpa.page_type_desc = N'DATA_PAGE';
OPEN cur;
FETCH NEXT FROM cur INTO @fileid, @pageid;
WHILE @@FETCH_STATUS = 0
BEGIN
    DBCC PAGE (@dbid, @fileid, @pageid, 3);
    FETCH NEXT FROM cur INTO @fileid, @pageid;
END
CLOSE cur;
DEALLOCATE cur;
DBCC TRACEOFF(3604);

Looking at the output for the first page from my demo (after the column is dropped, but before the column is updated), I see this:

PAGE: (1:100104)

BUFFER:

BUF @0x0000021793E42040

bpage = 0x000002175A7A0000          bhash = 0x0000000000000000          bpageno = (1:100104)
bdbid = 10                          breferences = 1                     bcputicks = 0
bsampleCount = 0                    bUse1 = 13760                       bstat = 0x10b
blog = 0x212121cc                   bnext = 0x0000000000000000          bDirtyContext = 0x000002175004B640
bstat2 = 0x0

PAGE HEADER:

Page @0x000002175A7A0000

m_pageId = (1:100104)               m_headerVersion = 1                 m_type = 1
m_typeFlagBits = 0x0                m_level = 0                         m_flagBits = 0xc000
m_objId (AllocUnitId.idObj) = 300   m_indexId (AllocUnitId.idInd) = 256
Metadata: AllocUnitId = 72057594057588736
Metadata: PartitionId = 72057594051756032                                Metadata: IndexId = 1
Metadata: ObjectId = 174623665      m_prevPage = (0:0)                  m_nextPage = (0:0)
pminlen = 8                         m_slotCnt = 1                       m_freeCnt = 79
m_freeData = 8111                   m_reservedCnt = 0                   m_lsn = (616:14191:25)
m_xactReserved = 0                  m_xdesId = (0:0)                    m_ghostRecCnt = 0
m_tornBits = 0                      DB Frag ID = 1

Allocation Status

GAM (1:2) = ALLOCATED               SGAM (1:3) = NOT ALLOCATED
PFS (1:97056) = 0x40 ALLOCATED   0_PCT_FULL                              DIFF (1:6) = CHANGED
ML (1:7) = NOT MIN_LOGGED

Slot 0 Offset 0x60 Length 8015

Record Type = PRIMARY_RECORD        Record Attributes =  NULL_BITMAP VARIABLE_COLUMNS
Record Size = 8015
Memory Dump @0x000000B75227A060

0000000000000000:   30000800 01000000 02000001 004f1f5a 5a5a5a5a  0............O.ZZZZZ
0000000000000014:   5a5a5a5a 5a5a5a5a 5a5a5a5a 5a5a5a5a 5a5a5a5a  ZZZZZZZZZZZZZZZZZZZZ
.
.
.
0000000000001F2C:   5a5a5a5a 5a5a5a5a 5a5a5a5a 5a5a5a5a 5a5a5a5a  ZZZZZZZZZZZZZZZZZZZZ
0000000000001F40:   5a5a5a5a 5a5a5a5a 5a5a5a5a 5a5a5a             ZZZZZZZZZZZZZZZ

Slot 0 Column 1 Offset 0x4 Length 4 Length (physical) 4

rid = 1

Slot 0 Column 67108865 Offset 0xf Length 0 Length (physical) 8000

DROPPED = NULL

Slot 0 Offset 0x0 Length 0 Length (physical) 0

KeyHashValue = (8194443284a0)

I've removed most of the raw page dump from the output shown above for brevity.  At the end of the output, you'll see this for the rid column:

Slot 0 Column 1 Offset 0x4 Length 4 Length (physical) 4

rid = 1

The last line above, rid = 1, returns the name of the column, and the current value stored in the column on the page.

Next, you'll see this:

Slot 0 Column 67108865 Offset 0xf Length 0 Length (physical) 8000

DROPPED = NULL

The output shows that Slot 0 contains a deleted column, by virtue of the DELETED text where the column name would normally be.  The value of the column is returned as NULL since the column has been deleted.  However, as you can see in the raw data, the 8,000 character long value, REPLICATE('Z', 8000), for that column still exists on the page.  This is a sample of that part of the DBCC PAGE output:

0000000000001EDC:   5a5a5a5a 5a5a5a5a 5a5a5a5a 5a5a5a5a 5a5a5a5a  ZZZZZZZZZZZZZZZZZZZZ
0000000000001EF0:   5a5a5a5a 5a5a5a5a 5a5a5a5a 5a5a5a5a 5a5a5a5a  ZZZZZZZZZZZZZZZZZZZZ
0000000000001F04:   5a5a5a5a 5a5a5a5a 5a5a5a5a 5a5a5a5a 5a5a5a5a  ZZZZZZZZZZZZZZZZZZZZ
0000000000001F18:   5a5a5a5a 5a5a5a5a 5a5a5a5a 5a5a5a5a 5a5a5a5a  ZZZZZZZZZZZZZZZZZZZZ


Database design: handling time I am developing a weekly timetable which ideally would allow teachers pick what subjects they teach. While most of this is fairly straightforward, from a conceptual point of view I am unsure how to handle time (which is, naturally, a fairly crucial aspect of the database).

Nobody can teach more than one thing at a given time, so having time as a primary key makes sense. Classes work on an hourly basis so integers are acceptable to this end. Times don't change from week to week.

While this seems fine on paper (and there is a quick mock-up of the database in Access below), this nonetheless has some additional baggage.

Monday 9am == 9. Monday 2pm == 14, etc. Cool. But Tuesday 9am == 33 and Tuesday 2pm == 38. A little bit awkward. Also, if one is using the integers for simple calculation (worked between 10 and 14?... 4 hours worked... correct), this can be easily messed up (worked all of monday and tuesday?... 48 hours worked... incorrect). But is integer still the best option for this type of data (given that date is unimportant)?

Data relating to class times will likely have to be manually input into the database. The database may have to be able to provide some sort of automation (automatically assign teachers to modules) and/or allow users to select modules to teach. RDMS likely to be MySQL.

Have a Class table, with a foreign key to Teacher - the person who taught the class.  Give Class columns for the start and end time. This can be either the MySQL time type (09:00:00 for 9am) or an integer type (9=9am, 23=11pm), it doesn't matter.  From these you can calculate the duration of a class.  The work a teacher performs is the sum of the duration of his classes:

select
    t.TeacherID,
    sum(c.EndTime - c.StartTime)
from Teacher as t
inner join Class as c
    on c.TeacherID = t.TeacherID
group by
    t.TeacherID

(Sorry if that doesn't parse - I work with SQL Server and am not familiar with MySQL syntax. Hopefully you get the idea.)

To cope with schedule conflicts add a day-of-week column to Class.  Enforce the rule about a teacher being in only one class at a time through triggers or a unique index.  Since your schedules are weekly the combination of day-of-week and start time would be a sufficient primary key.

As it is likely your school will offer the same subject more than once, you may find it beneficial to separate the concept of a class (Tuesday at 10am) from an actual instance (5th of March at 10am) to the delivery of that instance (Dave taught on 5th March at 10am).  A separate table for each of these would be best.


Remove Windows FCI completely When I built the cluster I forgot to uncheck the storage. It's now giving me all sorts of errors. How can completely remove the cluster so I can rebuild it?
This is a 3 node cluster and I evicted two nodes, but when I try to evict the last node I get:

I removed all the Disk and Pools from the remaining node.
Can you please guide me

In Failover Cluster Manager, right-click on the cluster, select More Actions, and click on Destroy Cluster.


PostGIS error building with `make` in ./extensions: nothing to be done for 'comments' I'm using code I checked out from SVN. Whenever I run make in ./extensions/, I get

 make
for DIR in postgis  postgis_tiger_geocoder  postgis_topology address_standardizer; do \
    echo "---- Making all in ${DIR}"; \
    make -C "${DIR}" all || exit 1; \
done
---- Making all in postgis
make[1]: Entering directory '/home/ecarroll/code/postgis/extensions/postgis'
mkdir -p sql_bits/
/usr/bin/perl -pe 's/BEGIN\;//g ; s/COMMIT\;//g' ../../postgis/postgis_for_extension.sql > sql_bits/postgis.sql
make -C ../../doc comments
make[2]: Entering directory '/home/ecarroll/code/postgis/doc'
make[2]: Nothing to be done for 'comments'.
make[2]: Leaving directory '/home/ecarroll/code/postgis/doc'
cp ../../doc/postgis_comments.sql sql_bits/postgis_comments.sql
cp: cannot stat '../../doc/postgis_comments.sql': No such file or directory
Makefile:72: recipe for target 'sql_bits/postgis_comments.sql' failed
make[1]: *** [sql_bits/postgis_comments.sql] Error 1
make[1]: Leaving directory '/home/ecarroll/code/postgis/extensions/postgis'
Makefile:26: recipe for target 'all' failed

Whenever I run make comments in the root I get a small portion of those errors,

make comments
make -C doc comments
make[1]: Entering directory '/home/ecarroll/code/postgis/doc'
make[1]: Nothing to be done for 'comments'.
make[1]: Leaving directory '/home/ecarroll/code/postgis/doc'

How do I resolve this?

You need xsltproc,

16:02 < robe2> EvanCarroll: Looks like you are probably missing xsltproc

Install xsltproc (instructions in Ubuntu/Debian)

sudo apt-get install xsltproc;

Then,

make distclean;
./autogen.sh;
make;

And try again. It should work.


Why can't we specify group functions in Oracle in the where clause? SQL> desc tab1
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                                 NUMBER
 NAME                                               VARCHAR2(1000)

SQL> select * from tab1;

        ID NAME
---------- ---------------
         1 a
         2 b
         3 c

SQL> select * from tab1 where id > AVG(id);
select * from tab1 where id > AVG(id)
                              *
ERROR at line 1:
ORA-00934: group function is not allowed here

SQL>

The error is very clear and says that this cannot be done. But I don't see why. The query makes perfect sense:

  select all rows from tab1 whose id  value is greater than the average

AVG and other aggregate functions work on sets of data.  The WHERE cause does not have access to the entire set, only to data for the row it is operating on.  If you created your own AVG function (as a normal function and not a custom aggregate function) it would only be passed one ID value when called from the WHERE clause not the entire set of ID values.

Mezmo's solution will give you your expected results, but if you want to avoid two full table scans (assuming no indexes) you can use a windowing function like this:

SELECT * FROM (SELECT AVG(id) OVER () avgid, t.* FROM tab1 t) WHERE id > avgid;


create column name from other table rows mySQL i have following code, for this i have two tables, one table contain questions and another table contain answers.
answers table something like following : (ans_question_1 to ans_question_95)

| answersId| ans_question_1| ans_question_2| student_id
|-----------|------------  |---------      |
| 1         | Male         | 23            |11
| 2         | Female       | 21            |12

Questions table (questions 1 to 95)

| question_id | Question|
|-----------  |--------
| 1           | Gender
| 2           | Area code

Want table something like this (need stored procedure with parameter student_id)
i also need this table creation dynamically, because my number of questions change regularly.

| student_id  | Gender    | Area Code
|-----------  |--------  -|--------
| 11          | Male      | 23
| 12          | Female    | 21

Using same stored procedure from previous answer, you can generate a new columns names, casting ans_question_xx with the name of each question.

create procedure getStudentAnswers(StudentId int)
begin

    set @cols := (select group_concat('ans_question_',
                                       cast(question_id as char(20)),
                                       ' as ',
                                       question separator ', ')
                  from questions);

    set @sql = concat('select student_id,  ',
                      @cols,
                      ' from   answers
                        where student_id = ', cast(StudentId as char(20)),
                      ' order by student_id;');

    PREPARE dynamic_statement FROM @sql;
    EXECUTE dynamic_statement;
    set @sql := null;
    set @cols := null;
    DEALLOCATE PREPARE dynamic_statement;

end \\

call getStudentAnswers(11);

call getStudentAnswers(12);

| student_id | Gender | Area_code | Another_question |
|------------|--------|-----------|------------------|
| 11         | Male   | 23        |                  |

| student_id | Gender | Area_code | Another_question |
|------------|--------|-----------|------------------|
| 12         | Female | 21        | null             |

Rextester here


Best practice for sharing data over differenet mysql/mariadb databases I have app that uses DB with 50+ tables. Now i'm in situation that i need to install another instance of app. Problem is that i would like to use some tables as "common" data, e.g. "brands" or "cities" or "countries" in both (for now there are only 2, but it might soon be more) apps.

I searched and find that i can make "common" DB with such tables, and have views in each DB instance that points to corresponding table.

Main app queries are heavily relying on that common tables, so i'm concerned if that will slow down my queries since views don't have indexes?

Are there some better practices? I'm looking now for replication in mysql manual. Is that way to go? Replicate tables from common DB to app instance DB's? Can this be one-direction replication? (only tables in "common" DB can be changed and then replicated to other DB's)?

thanx for advice Y

If the "common" tables on the secondary "app instances" are being accessed read only, then your easiest option will be doing a traditional master slave(s) configuration with a replicate_ignore_table = ... line put into all slaves for all tables except the "common" ones.

If you need to have identical behavior between all app instances (meaning you're not using app logic to isolate writes to the master only), then I would seriously considering using a galera cluster product of some sort.


What server property makes TransactionScope escalating to MSDTC? In our application we're using TransactionScope's. We're aiming to not use the MSDTC service because it's a lot slower than lightweight transactions.

using (var transactionScope = new TransactionScope())
{
  ...
  transactionScope.Complete();
}

While connected to the test database we need no MSDTC but the exact same code, executed on the same machine, escalates to use MSDTC when connected to the production or development database.

The development database is not clustered and isn't a named instance.

All are SQL Server 2008 (10.50.2500) and the connection strings are exactly the same except for the hostnames.

It seems there has to be some difference in the setup of the databases. Any ideas on what that could be?

EDIT

The transaction was there to protect inserts in two different tables. I noticed that there was also two calls that resulted in two selects in there. There was no need for these to be in the transaction so I moved them outside of it. The need for MSDTC then disappeared in all three databases. Problem solved but without really knowing what caused it.

Here are some of the things you can try to get it to work:

Compare Enlist=false in your connection strings
Compare MultipleActiveResultSets=true in your connection strings
User instance databases appear to work differently for some reason - we couldn't get it to work consistently with them - use full databases all of the time
You can reuse the same connection (close/reopen), or use more than one connection in a TransactionScope, but you cannot use more than one connection at the same time. You CAN however keep a ThreadStatic connection and pass that around (or, as we did, use ThreadLocal<> in a db wrapper class) - this solved a concurrency problem for us in ASP.NET which was causing unnecessary escalation
Don't bother if your db < 2008 or if compatibility level < 100
When using connection strings, don't use the ConnectionString property of another connection to create your new connection - it may not be the same as the original, which will cause escalation
Keep your connections open only for as long as you need to, close them at your earliest opportunity.

Stuff I found useful:

A ConnectionScope class gave me some ideas: http://blogs.msdn.com/b/dataaccess/archive/2006/02/14/532026.aspx
There's more to TransactionScope than the default ctor! http://blogs.msdn.com/b/dbrowne/archive/2010/06/03/using-new-transactionscope-considered-harmful.aspx
Note that there was nothing new I could find that would be different between 2008 and 2008 R2 - here is a list of stuff that was new in that release: http://msdn.microsoft.com/en-us/library/bb500435%28SQL.105%29.aspx


best ETL design to transfer transaction tables records into the data-warehouse I have 2 type of tables to populate the data-warehouse with every day, lookup tables or configuration tables with few 100s records, and thats easy where i just truncate and refill the table.

but for transaction tables, that have many records, i usually increment, that is i run the ETL daily to add yesterdays records.

i have 2 problems that i face always

when the job fails for any reason (i lose that Days transactions)
when for any reason the job run twice or i run it twice (i get duplicates)

now i am trying to design a way where i over come these 2 problems as well as am trying to develop the ETL in such a way that it can auto fix it self incase any of these events occur.

i want it to check if there are missing days and run run the ETL for that day, and check if there are duplicates and delete them.

below are ways i though of
1. i take in the last 5 days regardless, every day the ETL runs, deletes the last 5 days and refill.
2. i check the destination tables if they have missing dates in the last month and then i query the source with the missing days.

keeping in mind that the source is a huge table in a production environment that i have to optimize my query to the maximum when requesting from it.

thanks

Do the transactions have an audit timestamp ? It has to be one that goes up only (no late-arriving facts. insert/update audit timestamps are good for this)
If so you could use this to define a range to extract. It's a common technique for this type of thing:

For each extraction, at the start, determine the range you want to extract (let's call the minimum timestamp min_ts and the maximum timestamp max_ts)
At the start of the extraction, put 1 line in a separate table (let's call it extraction_log and also give it a PK) with fields: extraction_id = a unique key, min_ts, max_ts, status = 'Starting Extraction'.
Use min_ts & max_ts to extract the data, either in 1 go (select * from where ts > min_ts and ts <= max_ts) or in chunks if needed.
Ad the end of a successful extraction, update the line and set status to 'Finished OK'

how to determine min_ts and max_ts ?

You could take min_ts from the extraction_log, using the last successful max_ts. select max(max_ts) from extraction_log where status = 'Finished OK'
You could take max_ts from the source db at the start of your extraction. select max(audit_ts) from source_table

There are alternatives here. If you're extracting these into a temporary staging table (a best practice), you could also take too much, ie the last 5 days and deal with duplicates later when upserting the entries in your ODS. For max_ts, if you're absolutely certain that the clocks between your dwh and the source are and will remain in sync (this is a very dangerous assumption - not recommended), you could even use sysdate()

Technically, you can get away with less effort. You don't really need the statuses, or a extraction_log table that keeps track of each batch. But I have found having it like this helps a lot with debugging and troubleshooting later on.
Additionally, if you need a routine to remove entries from a load that failed halfway, to find gaps for ranges of past extractions, and so on, the extraction_log will help. You might even want to include the extraction_id as an additional column in your ODS.

some further thoughts

if you don't have a good timestamp candidate, a technical key in the source system with the same properties (must go up only, no late-arriving facts) should be fine too.
if the timestamps are generated by the source application and there is a risk that 2 transactions with the exact same timestamp are not inserted at the same time (quite common), then it's safer to use a max_ts that is slightly in the past (select max(max_ts) - 5 minutes from source_table.)
regarding failed loads. if you're only concerned with cleaning up entries of the last failed loads, you can add it as a first step in your ETL flow. (delete from dwh_table where ts > min_ts) This will remove the entries from any failures (if any) after the last successful extraction. It won't deal with failures between earlier successful extractions.


How to determine why connection creation lasts so long? On one of my servers (Windows 2008 Server), creating connection in Java to PostgreSQL server takes from 1,5 to 4 seconds! I have no clue how to start checking what can be wrong. I tried different database on this server, different user. Still the same. If I work with backup of database on my computer, it's 150ms. I would be grateful for any tip where to start.

I answer myself - it was SSL set to on.  Setting ssl to off in postgresql.conf solved the problem.


is id and phone number enough to uniquely identify a row in phone numbers Was designing a table a customer_phone_numbers table a while back, which only purpose was to allow us to store as many phone numbers as a user wanted to have on file. I originally came to the conclusion that customer_id + phone_number was unique, but then it occurred to me, it is possible for the same user to have 2 extensions at the same number. I am not sure how likely that is though, and it creates another issue.

phone_number ext
555-555-555, NULL
555-555-555, NULL

becomes legal, unless I find a good way to constrain ext against null. What is the best way to normalize and constrain phone numbers?

There are probably ways to solve this (e.g. an instead of trigger rather than an explicit constraint, or a clever filtered index if on 2008+), but why?

As an example, you can do this with two filtered indexes in 2008+:

CREATE TABLE #cpn(c INT, pn VARCHAR(32), x VARCHAR(8000));

CREATE UNIQUE INDEX x ON #cpn(c, pn) WHERE x IS NULL;
CREATE UNIQUE INDEX y ON #cpn(c, pn, x) WHERE x IS NOT NULL;

INSERT #cpn(c, pn   ) SELECT 1, '555-5555';        -- OK
INSERT #cpn(c, pn, x) SELECT 1, '555-5555', '345'; -- OK
INSERT #cpn(c, pn, x) SELECT 1, '555-5555', '555'; -- OK
GO
INSERT #cpn(c, pn   ) SELECT 1, '555-5555';        -- fails on index x
GO
INSERT #cpn(c, pn, x) SELECT 1, '555-5555', '345'; -- fails on index y

But I fail to see the point. This seems like unnecessary flexibility. If I'm going to call the office and try to reach Bob at extension 55, why would I be any more likely to reach him at extension 67? Does this seem like a feasible, real-world scenario to anyone?

I suggest you make it a business rule that a user can only have one extension at the same number. This way you can keep your constraint on customer id and phone number, and allow extension to be optional.

Also, assuming you are storing phone number and extensions as strings, you can go the other way and let people enter them together instead of separate. If they want to enter 15 different extensions at the same number, why stop them? I also think it's silly to allow an extension of 0, but this would allow that also.


Update statements with set transaction isolation level read uncommitted If I have a stored procedure with set transaction isolation level read uncommitted, will it affect update statements?

I know that you should not use with (nolock) on update/delete statements, and this does pretty much the same thing but not sure if SQL ignores it on update statements in the procedure or if I should be careful not to use it if there are update statements.

EDIT:

Sorry for the confusion. I'm not trying to figure out what the effect of using this type of locking on manipulation statements would be or whether it's a good idea. In fact I do NOT want to use this kind of locking on manipulation statements, and so my question is whether putting "set transaction ..." at the top of my stored proc is ever going to be honored by update/delete statements or whether it will be ignored. My hope is that it is just ignored.

I am fully aware of the effects (with its pros and cons) it will have on select statements.

If I have a stored procedure with set transaction isolation level read
  uncommitted, will it affect update statements?

Read uncommitted allows dirty reads. An X lock will be taken on the row or higher level (in the data page or index) before it is made dirty. Rows accessed directly by the query when locating a row to update will take a U lock and be blocked.

However it is still possible for an Update to be affected by the isolation level.

Connection 1

CREATE TABLE T1
(
X INT NULL,
Y INT NULL
);

INSERT INTO T1 DEFAULT VALUES;

BEGIN TRAN
UPDATE T1 SET X = 100;

WAITFOR DELAY '00:00:10'

ROLLBACK;

Connection 2 (run this within 10 seconds of firing off connection 1)

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

UPDATE T1
SET    Y = (SELECT SUM(X)
            FROM   T1);

SELECT *
FROM   T1;

Result

The read operation read the uncommitted value and the final result was used in the UPDATE even though the 100 that was read was eventually rolled back.


Disk Space Considerations for Rebuilding a large Clustered Primary Key Index We have a table in our database with over 200 million rows and a Clustered Primary Key Index which is over 100GB. The Primary Key is only so large because many more millions of rows have been archived from the table and the Index has not been rebuilt since. By rebuilding I'm hoping to reduce the size of the index considerably and free up a lot of space in our database.

The problem we have is the disk only has 20GB of free space and I'm worried how much will be temporarily required when rebuilding the index.

What is the best way to do this? Do I bite the bullet, rebuild the index and hope there is enough space? Or do I drop the PK and rebuild it from scratch?

Archiving Details
The archiving job is done on a datetime column and archives everything older than a year. This should run in parallel with the primary key which is an autoincrement since the datetime column is also autopopulated when a row is created.

DB, Table and Index Details
Row Count: ~212,300,000
Data space: ~109,000 MB
Index space: ~517 MB
Recovery model: Full

Data file free space: 5,991 MB / 151,000 MB
Data file drive space: 18.7 GB

Log file free space: 2275 MB / 25000 MB
Log file drive space: 79 GB

tempdb free space: 7476 MB / 19232 MB

There is only one index on the table, which is the clustered primary key:

Key Columns: 1x bigint
Sort: Ascending

We're using SQL Server 2008 R2 Standard Edition.

The clustered index "is" the table.  Rebuilding the index will require enough space to duplicate each existing non-deleted row, page-by-page.  Since you don't have Enterprise Edition, you do not have the luxury of rebuilding the index online.  This means the table will not be accessible during the rebuild operation.

You'd likely be better off rebuilding by creating a duplicate empty table on a different drive that has plenty of free space with room for foreseeable growth, moving data row-by-row in key order, then renaming the old and new tables.  For instance if the original table is Table1 and the new copy is Table2, rename Table1 as Table1-old then rename Table2 as Table1.


how to reduce the size of the datafiles of a oracle DB? we use a oracle db (11g release2) and the undo tablespase exists of two datafiles and requires 48 GB.
But we just need this size for the boot and the import.
Currently we just use 10 % from the datafiles...

Is it possible to minimize the datafiles online to the needed size ??

-- create a new UNDO tablespace named UNDOTBS2

CREATE UNDO TABLESPACE undotbs2
DATAFILE '/01/oradata/dbname/undotbs201.dbf'
SIZE 50M AUTOEXTEND ON NEXT 5M;

Tablespace created.

-- Switch the database to the new UNDO tablespace.

ALTER SYSTEM SET UNDO_TABLESPACE=UNDOTBS2 SCOPE=BOTH;

-- Drop the old one

DROP TABLESPACE undotbs1 INCLUDING CONTENTS AND DATAFILES;

-- If Undo is still in use

*
ERROR at line 1:
ORA-30013: undo tablespace 'UNDOTBS1' is currently in use

I would try this out first on a test database:

(Following from dead link) from original answer:

SELECT a.name,b.status
FROM   v$rollname a,v$rollstat b
WHERE  a.usn = b.usn
AND    a.name IN (
          SELECT segment_name
          FROM dba_segments
          WHERE tablespace_name = 'UNDOTBS1'
         );

The above query shows the name of the UNDO segment in the UNDOTBS1 tablespace and its status. See which users/sessions are running this pending transaction:

SELECT a.name,b.status , d.username , d.sid , d.serial#
FROM   v$rollname a,v$rollstat b, v$transaction c , v$session d
WHERE  a.usn = b.usn
AND    a.usn = c.xidusn
AND    c.ses_addr = d.saddr
AND    a.name IN (
          SELECT segment_name
          FROM dba_segments
          WHERE tablespace_name = 'UNDOTBS1'
         );

You can then kill the session; the following assumes a SID of 147 and a SERIAL# of 4:

alter system kill session '147,4' immediate;


Database replication and consistency check I got a question about two databases (in Oracle 10g) that I have, let's call them A and B. A have some information (in various tables) and I want B to get a partial copy of some tables from A, and constantly check changes in A and 'sync' them in B.

I want to ask you about some method, technique or maybe ideas, knowing that I can't make any change in A (just selects, no triggers).

I thank you help and patience (for possible edits) in advance.

Additional information:

Thanks for the answers, I don't know if it's relevant, but I found the MINUS operator, though I'm not sure if it works with a "sub table" (select).

Your options are rather limited because of your requirements to "constantly check ... sync" and "can't make any change in A".  Things such as materialized view logs, dbms_alert, streams, and a standby database are all off the table.

If the tables in A are constantly having all of their rows updated then (as Jack Douglas said) a materialized view would be the easiest to setup.  In the more likely event that most of the records don't change in A from moment to moment, you will probably want to setup a package (or packages) on B that select from A to merge and delete as necessary on B.  This will only be as up to date as the frequency in which it is run, but given your requirements it may be the best you can do.

Specifically, your package should do the following:

Delete from B rows that do not exist in A.
Merge A into B updating when matched and inserting when not matched.

If you want to avoid hitting the table in A multiple times you could insert the entirety of the table into a global temporary table on B and then do your Delete/Merge from there.

Concerning Minus:
Minus can tell you all the rows from a query of A that are not in B.  By union-ing this with B minus query of A you can get all rows that are different, but this would probably take longer to process even before adding the insert/update part.  If A doesn't get updates or deletes then you could insert the results of the first minus, but an insert into B...where not exists A... would still be faster and simpler.


MySQL Cluster over WAN: Best Architecture Options? My company is looking at a multi-data-centre solution for high-availability, fun, and profit.

We have businesses across the planet, with the need for MySQL database clustering solutions that can keep up with high-traffic over high-latency.  Bandwidth isn't really an issue for us.

Could you please recommend a MySQL Database Cluster Architecture that is able to cope with high-latency networks.  For instance, we have one Master at Sydney, AU, and another Master at New York, US - the latency between these two points can be up to a second or more.

We are currently using Percona Server 5.1, and we would like to stay this way, so I will place more value on answers that take this factor into consideration.

For high-latency the main choice is between Percona XtraDB Cluster (Galera+InnoDB-based) or the new WAN replication in MySQL Cluster 7.2 (NDB-based). The downside of NDB is greatly increased complexity and application changes; it is NOT a drop-in replacement for your existing system.  Percona XtraDB Cluster has only a few small differences, e.g. locking is pessimistic on the local node (as currently) but optimistic cluster-wide, so if there are conflicting changes on a remote node you won't find out until you commit. (If there are conflicting changes, the commit will fail and roll back; you can't break the cluster as you can with standard MySQL replication.)


Data impact on analyzing mysql table stats I have a MySQL database that appears to be getting slower over time. After some googling, I determine that the database probably needs to be analyzed. This is because innodb_stats_on_metadata is set as OFF, and I am not aware of any regularly scheduled analysis job running.

Here are a few of my problems:

How do I know for sure this db needs to be analyzed? It seems that MySQL doesn't store when the last time "stat" was updated, and this DB didn't have logging turned on.
This DB constantly have inserts/updates ongoing. I know during the analysis, nothing can write to the DB. What will happen to those write requests? will they get queued, or they will be thrown away?
Will mysqlcheck have any chance of breaking the DB? if so, how do I recover the data? This is a production database that I can't risk breaking...

Any help would be appreciated.

If you are using the InnoDB engine for your tables #1 is relevant.  If you are using MyISAM, #3 is relevant.  But...

You should "never" have to run ANALYZE TABLE.  (There are exceptions, but they are so rare, that I will assume that the problem is elsewhere.)

Possible cause #1:  Your data has grown to be bigger than the cache (buffer_pool).  How big is the data?  What is the value of innodb_buffer_pool_size?  Are you using InnoDB?  How much RAM?

Possible cause #2:  Some queries have less than perfect indexes.  Set long_query_time=1; turn on the slowlog; wait a day; look in the slowlog to see what is slowest; come back for advice.


How to copy bytea from Postgres to Oracle I have a db on Postgres 9.1. The db collected some binary data that are stored in bytea file.
I need to copy the data to Oracle 10g. How to do it?

The details

I have a table plines in Postgres9.1. plines contains a bytea  field shape.
The number of records is about 500.000.

I have a table olines in Oracle10g. olines contains a blob  field shape.

I need to copy data from  plines.shape to  olines.shape.

Here is a general way of getting a table with bytea data from postgres into a similar Oracle table with a blob.

It should be trivial to adapt my test to your real tables, and you should probably increase the size of the 'chunks' that I used to keep the output readable, from 20 to something more like the 2000 limit for string literals (4000/2 as each byte is 2 bytes hex).

1) postgres
testbed:

begin;
set role dba;
create role stack;
grant stack to dba;
create schema authorization stack;
set role stack;
--
create function random_bytea(p_length in integer)
       returns bytea language plpgsql set search_path to 'stack' as $$
declare
  o bytea := '';
begin
  for i in 1..p_length loop
    o := o||decode(lpad(to_hex(width_bucket(random(), 0, 1, 256)-1),2,'0'), 'hex');
  end loop;
  return o;
end;$$;
--
create function bytea_to_oracle_sql(p_id in integer, p_data in bytea)
       returns text language plpgsql set search_path to 'stack' as $$
declare
  o text := 'insert into foo(id, bar) values('||p_id||', empty_blob());';
begin
  for i in 0..(length(p_data)-1)/20 loop
    o := o||chr(10)||'update foo set bar=append_to_blob(bar,'''
          ||encode(substr(p_data,i*20+1,20),'hex')||''') where id='||p_id||';';
  end loop;
  return o;
end;$$;
--
create table foo(id serial, bar bytea);

insert test data and check hashes:

insert into foo(bar) values(random_bytea(90));
insert into foo(bar) values(random_bytea(90));
select id, md5(bar) from foo;
/*
 id |               md5
----+----------------------------------
  1 | 32a24c8827047eff517e8a40bf3ad8b4
  2 | 25ac852000889ab782ad6437accf1546
*/

generate Oracle script:

\o '/tmp/oracle.sql'
select bytea_to_oracle_sql(id, bar) from foo;
\o

contents of /tmp/oracle.sql:

insert into foo(id, bar) values(1, empty_blob());
update foo set bar=append_to_blob(bar,'ddbe858f7905551862507ddaa3b5410cba8013d4') where id=1;
update foo set bar=append_to_blob(bar,'089d6e66f16b10ba1204a4efc22a7f3c2fd45492') where id=1;
update foo set bar=append_to_blob(bar,'24d26dc887afd4412fe8163786859e77f0af3202') where id=1;
update foo set bar=append_to_blob(bar,'b3d7f2750172b2314606c36bd8313360e008a20f') where id=1;
update foo set bar=append_to_blob(bar,'f0b847763de6ee2e9521') where id=1;
insert into foo(id, bar) values(2, empty_blob());
update foo set bar=append_to_blob(bar,'3148f823befa95712bdc77ef4750207bb0018352') where id=2;
update foo set bar=append_to_blob(bar,'10730b58ea483ed876d4faa81df3ccdbed624d18') where id=2;
update foo set bar=append_to_blob(bar,'3ec40886142901c52a85173bf92393e36bd2bce2') where id=2;
update foo set bar=append_to_blob(bar,'a6b68ac3a9569f97b8ecaff7b1b87dc6e17f8b0b') where id=2;
update foo set bar=append_to_blob(bar,'04a347fdc6dae132ad9d') where id=2;

remove testbed:

rollback;

2) Oracle
testbed:

create table foo(id integer, bar blob);
--
create or replace
function append_to_blob(p_data in blob, p_append_hex in varchar) return blob is
  o blob;
begin
  dbms_lob.createtemporary(o, TRUE);
  dbms_lob.append(o, p_data);
  dbms_lob.writeappend(o, length(p_append_hex)/2, hextoraw(p_append_hex));
  return o;
end;
/

run script:

insert into foo(id, bar) values(1, empty_blob());
update foo set bar=append_to_blob(bar,'ddbe858f7905551862507ddaa3b5410cba8013d4') where id=1
...

check hashes:

select id, dbms_crypto.hash(bar,2) from foo;
/*
 ID DBMS_CRYPTO.HASH(BAR,2)
--- --------------------------------
  1 32A24C8827047EFF517E8A40BF3AD8B4
  2 25AC852000889AB782AD6437ACCF1546
*/

credit to Vincent Malgrat for his concat_blob function I adapted. See here for the random bytea function by itself


Slow SQL query from app, fast from SQL Studio I have a query that takes 30+ seconds when called from app, but takes <1sec when called directly from SQL SMS.

Here is the query :

SELECT T0.pk
    FROM   products T0
    WHERE  (( ( T0.pk IN (SELECT T1.target
                               FROM   catrel T1
                               WHERE  ( T1.source = 87960962008461 )
                                      AND ( T1.type = 87961011815221 ))
                AND T0.pk NOT IN (SELECT T2.target
                                       FROM   varexc T2
                                       WHERE  ( T2.source = 87960962008461 )
                                              AND (
                                      T2.type = 87961020990261 )) )
               OR ( T0.pk IN (SELECT T3.target
                                   FROM   varinc T3
                                   WHERE  ( T3.source = 87960962008461 )
                                          AND ( T3.type = 87961021066258
                                              )) ) )  )
           AND ( T0.type IN ( 87961022300198, 87916102426706 ) )
    ORDER  BY T0.p_code

I have no idea where to start debugging that. When run directly in the SQL databse, the response in instant :

The query does appear in the "recent expensive queries" (only when run from another client)

PS: SQL Server does not recommend any additional indexes.

The option PARAMETRIZATION was "forced" instead of "simple"
Switching back to simple + disabling Parameter Sniffing solved the issue.


Set up read-only privileges on the dbo schema, but allow creation of entities in another schema? Is it possible to create a custom schema in a database such that users that have read-only access in that database for the dbo schema can be set up with the ability to create and modify entities for the custom schema?

You shouldn't think about permissions in the sense that you have to own something to make changes to it. All you need to create an object in a schema is the ability to create an object in the database, and authorization on the schema (there are other ways but they are less secure). You also should think about applying the same permissions to n number of users via a role as opposed to applying those permissions individually to each user.

Here's a working example that demonstrates the syntax required for all of the things you want to do, I believe.

First, create logins from Windows, if they don't already exist:

USE [master];
GO
CREATE LOGIN [Domain\Username1] FROM WINDOWS;
CREATE LOGIN [Domain\Username2] FROM WINDOWS;

Add those users to the database (create this database as a test, perhaps):

USE a_database;
GO
CREATE USER [Domain\Username1] FROM LOGIN [Domain\Username1];
CREATE USER [Domain\Username2] FROM LOGIN [Domain\Username2];

Create a schema for them:

CREATE SCHEMA Pyramid;

Create a role, so that you don't have to maintain these permissions on every individual user, and add both users to that role:

CREATE ROLE PyramidRole;
GO
-- on modern versions:
ALTER ROLE PyramidRole ADD MEMBER [Domain\Username1];
ALTER ROLE PyramidRole ADD MEMBER [Domain\Username2];
-- on dinosaurs:
EXEC sys.sp_addrolemember @rolename = N'PyramidRole', @membername = N'[Domain\Username1]';
EXEC sys.sp_addrolemember @rolename = N'PyramidRole', @membername = N'[Domain\Username2]';

Set the permissions on the role:

-- need specific create / alter permissions:
GRANT CREATE TABLE, CREATE VIEW, CREATE FUNCTION, CREATE PROCEDURE
  TO PyramidRole;

-- to be explicit, you want them to /read/ from dbo, so:
GRANT SELECT, VIEW DEFINITION ON SCHEMA::dbo TO PyramidRole;

-- finally, this will allow them to create/alter objects in their schema:
ALTER AUTHORIZATION ON SCHEMA::Pyramid TO PyramidRole;
-- you may need other permissions depending on what else you need them to do

Now, try it out:

EXECUTE AS USER = N'[Domain\Username1]';
GO
CREATE TABLE dbo.what(id int);     -- Fails with Msg 2760
GO
CREATE TABLE Pyramid.what(id int); -- Succeeds
GO
REVERT;

Now, as you add more users and you want them to inherit this same set of permissions, you just add them to the database, and then add them to the role using ALTER ROLE ... ADD MEMBER ....

More info and more syntax examples:

Roles / ALTER ROLE
Schemas
Permissions
GRANT Database Permissions
DENY Database Permissions
EXECUTE AS


what effect does changing the data type of an existing indexed field have on the index? I have a few MySQL tables and I want to change the data type of some of the existing fields. The fields already have some single and multi-column indexes on them (all of which are type = "Normal").

I would like to know if changing the data types of any fields included in the existing indexes will have any effect on those indexes? Do I need to remove and re-create them for example or does MySQL handle that automatically?

Changing the data type of a column causes a table restructure, which will have the side-effect of rebuilding all indexes for the table (not only those referencing the column you altered, but all indexes in that table).

You don't need to drop or recreate the indexes yourself; it's already done by the time the ALTER TABLE finishes.


How to upgrade SQL Server 2016 and 2017 Standard to 2017 Enterprise edition We have one SQL Server 2016 Standard instance, and two SQL Server 2017 Standard instances installed on the same server. Now my management wants to install SQL Server 2017 Enterprise edition and upgrade all three instances to SQL Server 2017 Enterprise.

Please suggest the right procedure to completed this whole process.

I'm new to SQL Server DBA tasks, and I need some suggestions on how to upgrade SQL Server 2016/2017 Standard to SQL Server 2017 Enterprise.

Upgrading your two 2017 instances to Enterprise is a simple process. SQL Server Setup has a function called Edition Upgrade, which is a fairly straightforward process.

Your SQL 2016 Standard to SQL 2017 Enterprise is more complicated. You need to assess your environment and determine what best suits your resources, skills and appetite for risk. Microsoft has a good primer on this. You have two high-level options:

In-Place Upgrade
Migration

An In-Place Upgrade involves upgrading the installed SQL Server instance to a later version/edition in place on the same server. This is a somewhat simpler option but is higher risk. rollback is more difficult and from experience, the potential for things to go wrong are greater.

A Migration upgrade involves building a new SQL Server Instance and migrating your databases and server objects from the current instance to a new one. The advantage of a migration upgrade is that your original instance is left untouched, so rolling back usually involves little more than restoring your pre-upgrade backups and reconfiguring your application connection strings to point to the old server.

You also have the advantage of a 'slow burn' approach, where you can migrate individual databases, or subsets of databases, at a time. This is particularly helpful if your instance is shared by many applications because you can migrate the databases and objects for one application at a time, reducing the overall impact of the upgrade.

The downside to a migration approach is the need for additional hardware and licences for the period that both the old and new instances are running, plus the additional time taken to complete the 'upgrade'. Often this downside is outweighed by the risks involved with an In-Place Upgrade, but that is a business decision you need to determine within your team.

As you've stated in your comments, your goal is to put together a plan for your Senior DBA as part of a training assessment. You've not stated if this needs to be a high-level plan or a detailed plan, but either way, start with the primer from Microsoft and determine your preferred option based on risk appetite, allowable downtime, available resources (people and equipment) and your skillset. From here, you can develop a high-level plan for your Senior DBA to review and, if necessary, develop a detailed plan based on the feedback on your high-level plan.

Don't forget to include a plan for pre-upgrade activities, such as determining application support for the new version and edition, testing the process etc, and post-upgrade activities, such as assessing performance changes, determining if raising the compatibility levels is suitable and potentially enabling newly accessed features. For example, going to Enterprise Edition enabled setting up an Availability Group to take advantage of the advanced HADR capabilities of Enteprise Edition.


Resource semaphore query compile waits We have migrated SQL Server Standard Edition from 2008 R2 to 2014. After the migration, we are seeing a huge amount of blocking, and overall the instance is very slow.

The major wait we are seeing is Resource_Semaphore_Query_Compile. Some of the queries are requesting more memory and waiting on compile time.

This new server is a beast, Memory : 1TB and CPU : 2 physical cores with 30 logical processors each (60 total). Since this is Standard Edition, max memory is set to 128GB, and I believe there are some issues with maxdop setting and how SQL Server 2014 is utilizing the processors. Maxdop was set to 4 and we changed it to 2, still no luck.

Regarding the resource semaphore wait types: I updated stats on all databases with full scan. We cannot tune the queries as they are encrypted vendor code which used to perform perfectly well on SQL Server 2008 R2.

I cannot change the database compatibility level. I have enabled soft NUMA nodes on this instance by enabling startup trace flag 8079. It created 4 NUMA nodes (before the change, it was 2 NUMA nodes - I can see that in the SQL Server error log).

We are still seeing the Resource_Semaphore_Query_Compile waits. Some of the queries are requesting 24GB of memory.

RESOURCE_SEMAPHORE_QUERY_COMPILE isn't aided by more memory in older versions of SQL Server. Since you're on 2014, you can use Trace Flag 6498 if you're on certain patch levels.

That TF increases the large query compile gateway dependent on the amount of memory in your server.


Phone number with different formats lookup I have different phone formats stored in my db, such as :

727.170.4799
1-416-958-6390
1.561.374.4268
(813) 929-5892

There are approximately ~2 million records in this table so modifying would take a long time. And I need to find records by performing phone lookup.

One thing I control is the input, I can guarantee that it's going to be numeric. But what I don't know is whether they're going to type in numeric number with or without the country code so taking this number as an example 1-416-958-6390 both of these used in lookup:

14169586390
4169586390

Should return the same row (1-416-958-6390)

How do I do phone for phone numbers as efficiently as I can?

This is what I got but it's super slow (as expected) :

select * from patients
WHERE ( REPLACE(phone_number, '-', '') LIKE '%4169586390' )

To clean up the numbers use a regex that removes everything that's not a numeric digit:
regexp_replace(phone_number, '[^0-9]', '', 'g')

You can create an index on that, but a regular B-Tree index won't be used LIKE conditions where the wildcard is on the left.
In Postgres you have two choices: you can create a regular B-Tree index on the reverse value and use that instead:
create index on patients ( reverse(regexp_replace(phone_number, '[^0-9]', '', 'g')) );

Then use that expression and compare it with the reversed input:
select *
from patients
where reverse(regexp_replace(phone_number, '[^0-9]', '', 'g')) like reverse('4169586390')||'%'

The other choice is to install the pg_trgm extension and create a trigram index on the expression. That way you can use the input "as is":
create index on patients
   using gist ( regexp_replace(phone_number, '[^0-9]', '', 'g') gist_trgm_ops);

That index is then usable for:
select *
from patients
where regexp_replace(phone_number, '[^0-9]', '', 'g') like '%4169586390'

However, GiST indexes are bigger and a bit slower to maintain than B-Tree indexes.

If you don't want to repeat that regex expression in every query you can create a view that includes that, e.g.:
create view patients_clean_phones
as
select ... other columns ..., regexp_replace(phone_number, '[^0-9]', '', 'g') as clean_phone
from patients;

Then use (assuming you are using the GiST index, so no reverse required):
select *
from patients_clean_phones
where clean_phone like '4169586390%';

Postgres is smart enough to still use the index in that case.

If you are on Postgres 12, an alternative to the view is to use a computed column with the regex expression, then create the index on the computed column.


SQL Server Memory Performance I have two instances in one server. I configured  the max memory with the same number on each instance.  Total 36 Gigs and I gave 17 to each. Why the target memory keep coming up and down on one instance? (moves from 17 to 12 or between) but in other instance its fixed at 17? Also the total server memory is fixed at 10 Gigs.

I configured the max memory with the same number on each instance. Total 36 Gigs and I gave 17 to each

This is a poor configuration, change is ASAP. You have allocated 34 GB to SQL Server and just 2 GB to OS, in such scenario you are bound to starve OS from memory resulting in paging or memory trimming of SQL Server processes.

If you want to fix the memory for both the instances start with 15 GB for each instance and leave 6 GB for OS. This is considering that you do not have any other services being used like SSAS/RS/IS, if they are their, you need to give 2-3 GB more to OS so that these extra services can use it.

  Why the target memory keep coming up and down on one instance? (moves from 17 to 12 or between)

Target memory can change as per the load on the system, nothing alarming but, having said that you cannot just rely on one counter you have to look at lot of other counters related to memory.


In a DacPac deployment, if a stored procedure that is called by another stored procedure is updated, is the calling stored proc also redeployed? I noticed that, in my DacPac, after a deployment, two stored procs were given a modified date on sys.objects, almost to the same millisecond. However, I would only expect one of them to have this, since I only updated one proc. The other proc that was updated calls the one that was updated, but I didn't modify it directly.

Has anyone seen this behavior? Is it expected with DacPac, that the calling stored proc gets "modified" even though there is no actual code change, or could it be something else?

My concern is for a number of reasons, mainly related to QA people questioning this, and ultimately it isn't a good idea to see a proc has been modified when you can't explain why.

Any help appreciated.

Yes, it's expected.

If StoredProcA calls StoredProcB, and StoredProcB is modified, then the DacPac deployment will:

deploy the changes to StoredProcB
update the metadata for StoredProcA

What that looks like in the generated deployment script is:

GO
PRINT N'Altering [dbo].[StoredProcB]...';

GO
ALTER PROCEDURE [dbo].[StoredProcB]
/*
your new stored proc body here
*/
GO
PRINT N'Refreshing [dbo].[StoredProcA]...';

GO
EXECUTE sp_refreshsqlmodule N'[dbo].[StoredProcA]';

It's the call to sp_refreshsqlmodule that updates the modified date on StoredProcA.  The procedure hasn't been redeployed, but it's underlying metadata may have.

In your case, nothing has really changed.  The whole sp_refreshsqlmodule thing would be necessary if you updated a user-defined type that was used a parameter to StoredProcA, for example.  It's not necessary in this situation, but SSDT doesn't have complex rules about when to refresh dependent modules - it just updates them all if any have changed.


SQL-Server - Grant user DBO permission on database I have a reporting database that I restore every night from a backup of the production database.
When I restore, I need to add a user to the DBO role for that database.

I thought this would work:

use Restored_Prod
go
exec sp_addrolemember 'db_owner', 'chris'
go

But I get the following error, since that user doesn't exist in the Restored_Prod database:

Msg 15410, Level 11, State 1, Procedure sp_addrolemember, Line 75
User or role 'chris' does not exist in this database.

How do I add the user 'chris' to the database to be able to run the sp_addrolemember to make him a DBO of that database?
Is there a way to add the user permissions using sp_addrolemember command from the master db where the user exists?

User principals must exist in a database before you can grant them permissions.

use Restored_Prod
GO

CREATE USER [chris] FROM LOGIN [chris];
exec sp_addrolemember 'db_owner', 'chris';
GO

http://technet.microsoft.com/en-us/library/ms173463.aspx


Copying MySQL tables, InnoDB tables not recognized I have to set up a database copy on my local computer.
The problem is that that I have given just the raw database table files (.frm, .ibd, .MYD, .MYI).

I created the database by creating a folder in MySQL's data folder and moved all database files there.

The problem is that some tables seem to work correctly the ones with MyISAM storage engine
(.frm, .MYD, .MYI), I can access information in them, see their structure.

My problem is with the tables with InnoDB storage. I cant see their structure in MySQL workbench and when I try to select data from them MySQL spits out an error "Table 'table_name' doesn't exist".

There are two files per one InnoDB table (.frm and .ibd).

To fix this problem I tried to enable innodb_file_per_table, I added to my.ini file in MySQL root folder line innodb_file_per_table = TRUE but this does not solve anything.

I checked working InnoDB tables in my other MySQL databases and there are the same two files per table (.frm and .ibd).

I will include bellow screenshots of what I described for more clarity

The problematic table example:

Ok table example:

Could someone help me out please, Im hoping I was descriptive enough.

While .frm, .MYD, .MYI are enough for MyISAM, ibdata1 (InnoDB Global Table space) is needed for InnoDB even if you are using innodb_file_per_table = TRUE.

ibdata1 holds meta data for InnoDB Tables. InnoDB Table can't be found without those meta data. So you are getting "Table 'table_name' doesn't exist". error.

MySQL Manual states that

  You cannot freely move .ibd files between database directories as you can with MyISAM table files. The table definition stored in the InnoDB shared tablespace includes the database name. The transaction IDs and log sequence numbers stored in the tablespace files also differ between databases.

The main reason is Table id is different both source and dest. DB's ibdata1. So ibdata1 should be moved from src to dest.


Oracle 10g dump file push into Oracle 11g database Currently when executing the imp command with the following parameters:  file=my.dmp log=my.log fromuser=myuser touser=myuser

The following errors occur:

IMP-00058: ORACLE error 12560 encountered
ORA-12560: TNS:protocol adapter error
IMP-00000: Import terminated unsuccessfully

However, tnsping successfully looks up the values in my tnsnames.ora file.  My question is does the dump file created in 10g cause an issue on import into 11g or is this error something else that I am unable to diagnose.

Your problem might very well have to do with incorrect versions or wrong default connection. Make sure that you use the exp utility from the 10g installation and the imp utility from the 11g installation.
Also make sure that the environment variables like ORACLE_HOME are correct and that PATH is adjusted for the correct ORACLE_HOME/bin

How does your connect string look like?
Is your TNS_ADMIN variable defined? (if not, you might need to have multiple tnsnames.ora files in place, or tnsnames.ora is in a platform specific location (/etc, /var/opt/oracle etc.)) tnsping shows where it gets the sqlnet.ora (next to tnsnames.ora) from.

Even better is to start using expdp and impdp. You can use impdp to directly import your data in the 11g database, using a datasbase link.


Error ORA-12560 when trying to connect Windows SQL*Plus client to Oracle 11g Linux server I installed 11g on Centos VM on my mac.I have everything up and working on server.I have included following command I ran on cenos.

$lsnrctl status

LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 23-JUN-2014 14:49:41

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=hayer)(PORT=1522)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.1.0 - Production
Start Date                23-JUN-2014 14:03:58
Uptime                    0 days 0 hr. 45 min. 42 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/hayer/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=hayer)(PORT=1522)))
Services Summary...
Service "orcl" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...
Service "orclXDB" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...
The command completed successfully

My tnsnames.ora file is as follows:

# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

LISTENER_ORCL =
  (ADDRESS = (PROTOCOL = TCP)(HOST = hayer)(PORT = 1522))

ORCL =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = hayer)(PORT = 1522))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = orcl)
      (SID = orcl)
    )
  )

~
~
~
~

On Centos server I can log in using sqlplus too. I changed tnsnames.ora on windows to same as server.I have added oracle host information in windows host file and I can ping oracle host and telnet host on port 1522.

I have disables service iptables so no type of firewall exists on centos.

But I still get following error on Windows client:

SQL*Plus: Release 11.2.0.1.0 Production on Mon Jun 23 14:51:31 2014

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

Enter user-name: system as sysdba
Enter password:
ERROR:
ORA-12560: TNS:protocol adapter error

Enter user-name: system as sysdba
Enter password:
ERROR:
ORA-12560: TNS:protocol adapter error

On your Windows client, you need to specify the TNS alias @ORCL, or else it assumes you're trying to connect to a local database running on your Windows client, which there isn't one.  Also, system as sysdba generally isn't used, not even sure if that makes sense.  SYS as sysdba and just plain system are used.

Enter user-name: system@ORCL
Enter password:

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning option

If you don't have tnsnames.ora set up on your Windows client, you can still connect via EZCONNECT string @"hayer:1522/orcl" instead of setting up tnsnames (yes, the quotes are needed).

Enter user-name: system@"hayer:1522/orcl"
Enter password:

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning option


SQL command not properly ended Do you see why is appearing this error "SQL command not properly ended" in the select statement below?

Select command in oracle:

 select
        l_orderkey,
        sum(l_extendedprice * (1 - l_discount)) as revenue,
        o_orderdate,
        o_shippriority
    from
        customer,
        orders,
        lineitem
    where
        c_mktsegment = 'BUILDING'
        and c_custkey = o_custkey
        and l_orderkey = o_orderkey
        and o_orderdate < date '1995-03-15'
        and l_shipdate > date '1995-03-15'
    group by
        l_orderkey,
        o_orderdate,
        o_shippriority
    order by
        revenue desc,
        o_orderdate
     where rownum <= 10;

The syntax you used was invalid. This is how you select the first 10 rows in pre-12c versions.

select * from
(
 select
        l_orderkey,
        sum(l_extendedprice * (1 - l_discount)) as revenue,
        o_orderdate,
        o_shippriority
    from
        customer,
        orders,
        lineitem
    where
        c_mktsegment = 'BUILDING'
        and c_custkey = o_custkey
        and l_orderkey = o_orderkey
        and o_orderdate < date '1995-03-15'
        and l_shipdate > date '1995-03-15'
    group by
        l_orderkey,
        o_orderdate,
        o_shippriority
    order by
        revenue desc,
        o_orderdate
)
     where rownum <= 10;


Set Value Based on Number of Records with Certain Values using SQL Server I am stuck on how to return a bit value based on records with certain values.  Query needs to return TRUE or FALSE if there are three different Levels of the same Control:

Example data:

PRODUCT    CONTROL    LEVEL
test1      8          1
test1      8          2
test1      8          3
test2      8          1
test2      8          1
test2      8          3
test4      8          1
test4      8          3
test4      8          7

I need a query that would return:

PRODUCT    STATUS
test1      PASS
test2      FAIL
test4      PASS

Not sure where to begin.  Any tips are greatly appreciated.

This query will return PASS if there are at least three unique levels, otherwise FAIL.

SELECT PRODUCT, (CASE
       WHEN COUNT(DISTINCT [LEVEL])>=3 THEN 'PASS'
       ELSE 'FAIL' END) AS [STATUS]
FROM tablenamegoeshere
GROUP BY PRODUCT, [CONTROL];

COUNT(DISTINCT ..) counts the number of unique values of a column or expression. CASE returns different values depending on the outcome of different conditions. In this case, if the number of unique values is equal to or greater than three, we want to return 'PASS' otherwise 'FAIL'.

Obviously, if you want to return a bit value (i.e. boolean), you could change the strings 'PASS' and 'FAIL' in the code into 1 and 0 respectively and cast them to bit using CAST(... AS bit).


Huge logs are generated in mongo db shard logs We are using Mongo db Shard set up. What i see that on primary of each shard log file keep growing to GBs. Even in a day it reaches to 10GB for single day.

This appears to be slowing down Mongo over time.

I would like to have Mongo automatically rotate the log once it reaches, say, 100MB.  I've looked at the Mongo documentation, the standard Mongo package does not appear to support automatic log rotation based on size.

I also need to know why log file on primary grows so much.

Please help us with this.

Thanks

If you are using Linux or Unix as your platform, you could setup a shell script that monitors the size of the log files, and send the SIGUSR1 signal to mongod which forces it to cycle the log files once the files reach a certain size.

This Mongo documentation shows how to send the signal here

kill -SIGUSR1 <mongod process id>

You can get the <mongod process id> using:

ps -e | grep -i "mongod"


Redirecting MySQL errors and slow logs into another database I am running a number of MySQL servers as slaves and masters. It is quite annoying to look for slow logs and errors in every server, so I would like to log all the errors into an external database.

    DB1    DB2    DBn ...
      \      |      /
       \     |     /          <-- error logs and slow logs
        \    |    /
[MySQL error & slow log database]

Is there some simple way of doing it?
Or I do need to write some scripts and stuff?

P.S. I hope you appreciate my superb ASCII diagraming skills

Have you ever considered converting the slow log into a MyISAM table?

As for MySQL 5.1.30, you can choose the format of the slow log and general log as either TABLE or FILE by means of the log_output option. It is also available in MySQL 5.5.

To support the TABLE option for log_output, MySQL provided two tables:

mysql.slow_log
mysql.general_log

For the sake of this question, I will discuss only mysql.slow_log

By default, this is mysql.slow_log:

mysql> show create table mysql.slow_log\G
*************************** 1. row ***************************
       Table: slow_log
Create Table: CREATE TABLE `slow_log` (
  `start_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `user_host` mediumtext NOT NULL,
  `query_time` time NOT NULL,
  `lock_time` time NOT NULL,
  `rows_sent` int(11) NOT NULL,
  `rows_examined` int(11) NOT NULL,
  `db` varchar(512) NOT NULL,
  `last_insert_id` int(11) NOT NULL,
  `insert_id` int(11) NOT NULL,
  `server_id` int(10) unsigned NOT NULL,
  `sql_text` mediumtext NOT NULL
) ENGINE=CSV DEFAULT CHARSET=utf8 COMMENT='Slow log'
1 row in set (0.10 sec)

mysql>

The slow_log table uses the CSV storage engine. Please note that a CSV table cannot be indexed. That's not very useful for doing analysis by date and time. GOOD NEWS: You can convert it to a MyISAM table.

In order to facilitate a MyISAM-based slow log, perform the following:

Step01) Make mysql.slow_log a MyISAM table

ALTER TABLE mysql.slow_log ENGINE-MyISAM;

Step02) Index to timestamp column called start_time

ALTER TABLE mysql.slow_log ADD INDEX (start_time);

Step03) Add the following lines to the my.cnf (or my.ini)

[mysqld]
slow-query-log
log-output=TABLE

Step04) Restart MySQL

For Linux

service mysql restart

or for Windows

net stop mysql
net start mysql

Step05) Run the following command in MySQL

mysql> SHOW VARIABLES LIKE 'long_query_time';

By default long_query_time is 10 seconds

Step06) Run this command

mysql> SELECT SLEEP(15);

If long_query_time is 10 seconds, this SELECT should deliberately be placed in mysql.slow_log.

Step07) Run this command

mysql> SELECT sql_text FROM mysql.slow_log;

If you see any row in the table with the SELECT SLEEP(15);, CONGRATULATIONS !!!

You can now run SELECT queries against mysql.slow_log to copy some or all of the rows to other tables or other databases.

I have posted past answers in conjunction with this subject:

Mar 30, 2012 : MySQL slow log configuration
Nov 18, 2011 : Create a custom log table in MySQL
Jun 30, 2011 : How do I output MySQL logs to syslog?

UPDATE 2012-11-12 17:00 EDT

In all fairness, the question did ask about error logs. I did not explicitly include anything about it. I will include some code that addresses how to

Let's say the error log is defined on any DB server as follows:

[mysqld]
log-error=/var/log/mysqld.log

You could create a script like this:

ERROR_LOG=/var/log/mysqld.log
LOG_LINES=/tmp/general-log-lines.txt
ERROR_FOLDER=<folder where to copy logs>
ERROR_TARGET=${ERROR_FOLDER}/`hostname`-DB-Errors.txt
NEWCOUNT=`wc -l < ${ERROR_LOG}`
if [ -f ${LOG_LINES} ]
then
    OLDCOUNT=`cat ${LOG_LINES} | awk '{print $1}'`
    if [ ${OLDCOUNT} -lt ${NEWCOUNT} ]
    then
        (( DIFF = NEWCOUNT - OLDCOUNT ))
        tail -${DIFF} < ${ERROR_LOG} >> ${ERROR_TARGET}
        echo ${NEWCOUNT} > ${LOG_LINES}
    fi
fi

or if you want to collect and reset the error log without a mysql restart (linux only) you can use this (This technique could never be done in Windows because the Windows OS locks the error log file from external writing):

ERROR_LOG=/var/log/mysqld.log
LOG_LINES=/tmp/general-log-lines.txt
ERROR_FOLDER=<folder where to copy logs>
ERROR_TARGET=${ERROR_FOLDER}/`hostname`-DB-Errors.txt
NEWCOUNT=`wc -l < ${ERROR_LOG}`
if [ -f ${LOG_LINES} ]
then
    OLDCOUNT=`cat ${LOG_LINES} | awk '{print $1}'`
    if [ ${OLDCOUNT} -lt ${NEWCOUNT} ]
    then
        (( DIFF = NEWCOUNT - OLDCOUNT ))
        tail -${DIFF} < ${ERROR_LOG} >> ${ERROR_TARGET}
        echo -n > ${ERROR_LOG}
        echo 0 > ${LOG_LINES}
    fi
fi

Place this script on every DB server. Setup a crontab for this script to go off every 5 minutes. Make sure ERROR_FOLDER is set to a common shared folder.

UPDATE 2012-11-12 17:24 EDT

For clarification, running MySQL FLUSH LOGS; is insufficient because...

For all text-based logs, FLUSH LOGS; closes and reopens. No rotation is provided by mysqld
For binary logs, FLUSH LOGS; will...

Close current binary log
Rotate to a new binary log
Open new binary log

For MySQL 5.5, FLUSH ERROR LOGS; closes and reopens the error log. No rotation is provided

This is why some scripting is necessary


SQL Server 2012 performance v SQL Server 2008 R2 Does anyone have any links to performance benchmarking of the database engine, or any anecdotal reports of the performance improvements in SQL Server 2012. We are only using the DB engine,not reporting services or BI. Any advice / gotchas from an upgrade point of view?

There are some underlying performance enhancements in filestream and full-text search. I don't have the details handy. I am also sure there are other underlying enhancements that aren't publicized, as well as fixes from 2008/R2 that made it into service packs and cumulative updates on those platforms that were ported forward into SQL Server 2012. That would be a bit more quiet because while 2012 was in development there wasn't a need for a public KB article for every change.

Some of the new windowing functionality will make calculations like running totals perform way better. And OFFSET can be used to make child's play out of things like calculating a median. FORCESEEK also allows you to now actually specify the index you want used, instead of taking whatever SQL Server thought you meant. But those require code changes.

There is also going to be more predictable performance with regard to memory use - the memory manager now handles more than just single-page allocations, so "Max server memory" has a much more accurate meaning than it has in prior versions. Resource Governor memory settings also take advantage of this, and can be used for scheduler/NUMA affinity as well.

Troubleshooting performance is also a lot easier. For example there is a lot more information included in execution plans, and extended events has almost full parity with trace (and adds much more powerful causality tracking).

I was surprised that they didn't publish TPC stuff well in advance of the release date (not that those tests really represent real-world stuff, but it's fun to watch the "my dad can beat up your dad, at least today" arguments unfold between the vendors). Glenn Berry blogged about one of the SQL Server 2012 TPC-E results. I apologize but I don't have time to investigate whether there is an easy way to compare this apples to apples against similar benchmarks using 2008 or 2008 R2. The problem with aged benchmarks is that they're never on the same hardware, so while the new benchmarks are always faster (or else they won't get released), it's impossible to tell how much of the improvement was based on the hardware and how much on the software.

Your best bet is to install SQL Server 2012, restore a copy of your database, update compat level, update statistics, and run your worklaod on your hardware using your data.


Executing the same SQL statement for different tables I have a stored procedure that deletes data from multiple tables.  The SQL is all the same, except for the name of the table.

UPDATE desttable
SET desttable.reportarea = esz2reportarea.reportarea
FROM table1 AS desttable JOIN esz2reportarea ON desttable.subdivisn = esz2reportarea.esz
WHERE desttable.reportarea = ''

UPDATE desttable
SET desttable.reportarea = esz2reportarea.reportarea
FROM table2 AS desttable JOIN esz2reportarea ON desttable.subdivisn = esz2reportarea.esz
WHERE desttable.reportarea = ''

...

UPDATE desttable
SET desttable.reportarea = esz2reportarea.reportarea
FROM table10 AS desttable JOIN esz2reportarea ON desttable.subdivisn = esz2reportarea.esz
WHERE desttable.reportarea = ''

I'd like to rewrite this to use some kind of loop, so that the SQL is only written once.  (This is for SQL Server 2008R2.)

The list of tables that need to be updated will remain static; if there are any changes to the list of tables to be updated, it would be due to an application upgrade, and I'd have to redetermine which tables need to be modified at that time.  The list of tables to be updated will be placed into a temporary table.

SELECT * INTO #tablenames
FROM (VALUES ('table1'), ('table2'), ('table3')) AS tables(tablename)

You dont need a loop for what you want. Dynamic SQL will help you want you want to achieve.

SELECT * INTO #tablenames
FROM (VALUES ('table1'), ('table2'), ('table3')) AS tables(tablename)

--select * from #tablenames

declare @sqltext nvarchar(max) = N''
select @sqltext += 'UPDATE  desttable
SET desttable.reportarea = esz2reportarea.reportarea
FROM '+quotename(tablename)+ ' table1 AS desttable JOIN esz2reportarea ON desttable.subdivisn = esz2reportarea.esz
WHERE desttable.reportarea = '''';'+char(10)
from #tablenames
order by tablename

print @sqltext

-- once you review the above print statement and are HAPPY, uncomment the below part.
-- Below part will actually RUN the UPDATE statement ...
-- exec sp_executesql @sqltext

Note: I agree with @AaronBertrand that you should create a physical table - that will be a driver table for the update statement and just update the table when you want to modify or insert more values if you want other tables to be updated.


Altering strings while selecting them I'm working in SQL Server 2008, I have a varchar(255) column in my table, I want to select out all of the strings which contain the ':' character, and select the first part of the string before (but not including) the ':' character into a table variable so I can then work with this new data set. I have a piece of code which selects the substring I want from a string variable but I don't know how to do this as part of selecting all of the required strings in one go from the original table.

IF PATINDEX('%:%',@Login) > 0
BEGIN
    set @Login = SUBSTRING(@Login,0,PATINDEX('%:%',@Login))
END

I would prefer to not to have to loop over the table but I am not experienced with sql and so am not sure what is possible and what is not

So if I have in my original table:

Login varchar(255)
-----
dn\jblogs
dn\frobins:21 jul 2013
dn\tbecker
dn\ghammer:17 jul 2013

then the script should pull out into a table variable:

StrippedLogin varchar(255)
-------------
dn\frobins
dn\ghammer

DECLARE @x TABLE(StrippedLogin VARCHAR(255));

INSERT @x(StrippedLogin)
  SELECT LEFT([Login], CHARINDEX(':', [Login])-1)
  FROM dbo.OriginalTable
  WHERE CHARINDEX(':', [Login]) > 1;


How to count rows after a specific row? I have a contact mechanism between my website's users (always between two users). User A (sender) can send a message to users B (receiver) based on two rules:

User A could send 2 messages at most to user B and he must receive at least one message from user B to be able to send another 2.
User A could send 4 messages at most to everybody in daily period, not more.

And here is my table structure:

-- contact
+----+-----------+------------+--------------------------+------------+
| id | sender_id | receive_id |          message         |  date_time |
+----+-----------+------------+--------------------------+------------+
| 1  | 123       | 456        | Hi, how are you?         | 1492431111 |
| 2  | 123       | 789        | How are you doing?       | 1492431112 |
| 3  | 456       | 789        | Why would you say that?  | 1492431113 |
| 4  | 123       | 456        | Why don't you answer?    | 1492431114 |
| 5  | 789       | 456        | Because the sky is high  | 1492431115 |
| 6  | 123       | 789        | Hello?                   | 1492431116 |
+----+-----------+------------+--------------------------+------------+

And here is my current query:

INSERT INTO contact(sender_id, receive_id, message, date_time )
SELECT ?, ?, ?, unix_timestamp()
FROM dual
WHERE NOT EXISTS(
    SELECT count(*) AS num_day,
    FROM contact
    WHERE user_id = ?
      AND date_time > unix_timestamp(DATE_SUB(now(), INTERVAL 1 day))
    HAVING num_day > 4
)

As you can see, only the second rule is implemented in my query. How can I also implement the first rule to the query?

I always find views and subqueries easier to read and develop mainly as you can test and edit one section at a time. Depending on the number of messages and your system it may be a little slower though I doubt it. In this case I am going to use a union query.

I would also check first and if the limits are not exceeded then perform the insert (unless you doing millions of messages).
Create a view of the total number of sent messages

 CREATE view vwMessagesSent AS
 SELECT *
 FROM `contact`
 WHERE date_time > unix_timestamp(DATE_SUB(now(), INTERVAL 1 day))

This reduces the overall complexity of

SELECT Least(4,4 - count(id)) as sendLimit
FROM `vwMessagesSent`
WHERE user_id = ?
UNION ALL
SELECT Least(2,
       IF(
          (SELECT COUNT(id) FROM `vwMessagesSent` WHERE receiver_id = ? AND  user_id = ?)=1, 4, 2)
       )  - (SELECT COUNT(id) FROM `vwMessagesSent` WHERE user_id = ? AND receiver_id = ?)
)
FROM `vwMessagesSent`
WHERE user_id = ? AND receiver_id = ?
order by SendLimit
LIMIT 1

Note: Not tested - should be close to what you are after

We are using a few interesting SQL items out of the box.
Least takes the lowest number of a collection of numbers. It allows us to only return the number of messages the user is allowed to send and for you to warn the user that the insert/message send was not going to be undertaken as they are over quota.

UNION allows you to merge two queries and we use the ALL to make sure we have multiple rows (rather than using the distinct key

LIMIT 1 We sort the order of the two rows returned with the minimum number available to be sent and then only return that row.

Hope this helps. Happy to clarify or fix the main SQL and I would be looking at using at a stored procedure to improve the logic even further.


Storing huge amount of user game data So I have this side project of mine which relies on me agreggation User game data from an API.

What I want to store is the player encouters with other players and saving the result of those encounters aswell. The issue is that the only solution i have managed to come up is to create a Table for each User and have a 3 columns so far, IDPlayer (the player that was played against), GamesWon (the number of games won) and GamesLost (the number of games lost).

This wouldn't be an issue if my expected Player count was above 200k which would mean I would need to create at least 200k tables and each table could theorically contain 200k entries (if those players face each other which is unlikely but still possible). I have read that there could be performance problems once the number of Table have reached 10k.

My question is, is there a better way to handle this sort of information, am I on the wrong path ?

First of all, you need to stop thinking in matrices.

You have 2 options. 1) Store each game & who won. 2) Store aggregates of who won.

Option 1

Store every game and the result of said game. Also allows more data about each game to be recorded within the games row.

create table players
(
  player_id integer,
  player_username varchar(50),
  other_player_related_column datatype,
  ...
  ...
  ...
);

create table games
(
  player1_id  integer, -- references players table
  player2_id  integer, -- references players table
  winner_id integer, -- the player_id of the winner
  interesting_game_fact datatype -- other game info
);

Option 2

Just store aggregates of the results of games played between two players. Losses can be derived from the number of games the opponent won. There should only be 1 row for each player_id pair - I suggest storing the lower of the two player_ids as player1_id, just to make life easier.

create table players
(
  player_id integer,
  player_username varchar(50),
  other_player_related_column datatype,
  ...
  ...
  ...
);

create table results
(
  player1_id  integer, -- references players table
  player2_id  integer, -- references players table
  player1_wins integer, -- number of times player 1 has won
  player2_wins integer -- number of times player 2 has won
);


Select from linked server to local server causing waits on both sides The queries in question are here: http://pastie.org/4833351
Run individually, each of these queries runs through (albeit slow).
Run as a group, it just sits there, showing either OLEDB waits on the local box or in a SUSPENDED state on the linked server (wait type: ASYNC_NETWORK_IO).
The query finished (after 44 mins) with the following errors:

OLE DB provider "SQLNCLI10" for linked server "[MyServer]" returned message "Protocol error in TDS stream".
OLE DB provider "SQLNCLI10" for linked server "MyServer" returned message "Query timeout expired".
Msg 7330, Level 16, State 2, Line 127 Cannot fetch a row from OLE DB provider "SQLNCLI10" for linked server "MyServer".

Can anyone here point me in the direction of how I can both diagnose and fix this?

Pulling data through  a linked server is unlikely to be quick especially if you are joining remote tables to local tables. (Just use Profiler to watch what your local server sends to the remote server and I think that you'll be convinced.) Linked Servers are convenient and often "good enough", but not when you start blowing through your batch processing windows.

I have seen overall processing go faster by pulling the remote data into local (temporary or permanent) tables and then "doing the joins" locally to figure out what to put into the production tables.

If you have a large amount of data, I suggest looking into using a SSIS package or bcp and probably bringing the data into a local staging table first and converting the code to run off of local tables. This might involve a lot of surgery to your jobs since you need to have a job step (or steps) to run bcp code and/or packages.

If you are using SQL 2008 or better, MERGE should be available and it could provide more efficient operations. I am unsure if MERGE works with tables on a linked server and, if it does, performance may not be much better than what you have. So, you are still stuck getting the data onto the local server.

Also, you could look into partition switching, but I think would be the most costly thing to do in terms of time spent on getting things to work. You could view it as a learning experience.

Alternatively, you might consider increasing the query timeout, which is a server-level setting on the local server. That might help with the timeout error (until your data volume increases, then you would need to tweak it again), but I doubt that it will help with the TDS error problem.

If you want to minimize the length of time that your production tables are locked (due to the INSERT, not the SELECT with nolock), you would want to get all of the data from the ETL system onto the local box first, then go and insert it all. You want to avoid pulling data for one table, inserting that, pulling the data for the next table, inserting that, etc. (I'm presuming that you are doing all of this in a declared transaction that I'm not seeing.)


Error in partition switching in SQL Server 2014 when migrating a database from SQL Server 2005 I am migrating an old database from SQL Server 2005 to SQL Server 2014. This database has a partitioned table; a scheduled job periodically moves old data from the "online" table to the the historical table.

The job fails when I try to move the partition with the following code:

ALTER TABLE Warehouse.TBW_MY_DATA
SWITCH PARTITION 1
TO Warehouse.TBW_MY_DATA_STAGING

Here is the error message:

  Msg 4939, Level 16, State 1, Line 4
  ALTER TABLE SWITCH statement failed. index 'MY_DATABASE.Warehouse.TBW_MY_DATA_STAGING.PKW_MY_DATA_STAGING' is in filegroup 'FG_EDRN_2009123100085' and partition 1 of index 'MY_DATABASE.Warehouse.TBW_MY_DATA.PKW_MY_DATA' is in filegroup 'FG_EDRN_2007033100002'.

Actual screenshot of the error:

MY_DATABASE.Warehouse.TBW_MY_DATA_STAGING.PKW_MY_DATA_STAGING and MY_DATABASE.Warehouse.TBW_MY_DATA.PKW_MY_DATA are the clustered indexes of the primary keys.

What I tried:

To isolate the problem I run the job with different compatibility levels.

The error only occurs using compatibility level 120 (SQL Server 2014). Everything works fine with compatibility level 100 and 110 (respectively SQL Server 2008 and SQL Server 2012).
So probably something changed in partition handling from SQL Server 2012 to SQL Server 2014.
I read the article about "Transferring Data Efficiently by Using Partition Switching" on TechNet, but no deprecation date/version is ever mentioned even if I noticed that the article is available only for SQL Server 2005 and 2008.
I checked the list of deprecated features and the list of breaking changes in SQL Server 2014, but I found nothing concerning partition switching.

Actually, I think this is the opposite of a bug. the 2008 document states "Source and target tables must share the same filegroup" but it looks as though they only started paying attention to their rules in 2014. This article (made for 2014 / 2016 CTE) states "The corresponding indexes, or index partitions, must also reside in the same filegroup" TechNet


00911. 00000 - "invalid character" I am completely new to Oracle DB Administration an PL/SQL.

I get an "invalid character" error trying to execute the following code in Oracle SQL Developer.
This is my first statement in PL/SQL.

SET SERVEROUTPUT ON

DEF username = 'cuser2'

DECLARE

   i_matching NUMBER(1) := null;
  --  username Varchar(20) := 'cuser2';

BEGIN

   SELECT COUNT (1) INTO i_matching FROM dba_users WHERE username = UPPER('&username');

  DBMS_OUTPUT.put_line(i_matching);

   IF i_matching = 0 THEN

    EXECUTE IMMEDIATE ('
    create user &username IDENTIFIED BY &username;
    GRANT CONNECT,RESOURCE,UNLIMITED TABLESPACE TO &username;
    ALTER USER &username DEFAULT TABLESPACE USERS;
    ALTER USER &username TEMPORARY TABLESPACE TEMP;
    ');

   END IF;

END
;
/

I definitely lack knowledge about the PL/SQL syntax and created this code from examples in the web.
I guess the error is because of a misplaced ';' somewhere but I don't get it.
It would be great to get a hint on what I am missing here.

This kind of code pretty much seems to be the only way to implement a 'create and alter user if not exists' behaviour. At least it's the only method I found on the web.

Coming from sripting languages I naively expected something like the following to work but I guess nothing similar is implemented in Oracle:

-- create and alter user if not exists
IF (NOT EXISTS user cuser1) THEN

create user cuser1 IDENTIFIED BY cuser1;
GRANT CONNECT,RESOURCE,UNLIMITED TABLESPACE TO cuser1;
ALTER USER cuser1 DEFAULT TABLESPACE USERS;
ALTER USER cuser1 TEMPORARY TABLESPACE TEMP;

END IF;

Thank you in advance for any hints.

Thanks to Phil's comment the correct statement looks like this:

SET SERVEROUTPUT ON

DEF username = 'cuser2'

DECLARE

   i_matching NUMBER(1) := null;

BEGIN

   SELECT COUNT (1) INTO i_matching FROM dba_users WHERE username = UPPER('&username');

  DBMS_OUTPUT.put_line(i_matching);

   IF i_matching = 0 THEN

    EXECUTE IMMEDIATE ('create user &username IDENTIFIED BY &username');
    EXECUTE IMMEDIATE ('GRANT CONNECT,RESOURCE,UNLIMITED TABLESPACE TO &username');
    EXECUTE IMMEDIATE ('ALTER USER &username DEFAULT TABLESPACE USERS');
    EXECUTE IMMEDIATE ('ALTER USER &username TEMPORARY TABLESPACE TEMP');

   END IF;

END
;
/