Search for the root words using root words with prefixes or suffixes

What you're talking about is "stemming" where a word -- refill -- is related to its "root" or "stem" word -- fill. This is usually implemented through Full Text Search, which I believe SQLite supports, though I've never used it.

To implement this yourself in your own tables and code will be a complex task requiring a thorough understanding of the target language(s), how word-forms are constructed and how the grammar is defined, both theoretically and in practice.


MS Access Many-Many Query/Form Assistance

I'm having to do a little bit of psychic debugging here, but generally speaking, you should have the subform bound to a query that joins your junction table to the table of related records. The form inside the subform shouldn't know about the table in the left side of the relationship (tbl_apptltrs).

So the chain is something like this:

Main form bound to tbl_apptltrs.
Subform linked to main form via primary key of tbl_appltrs (e.g. parent form ApptLtrsID = subform LinkedApptLettersID).
Form inside subform bound to query that joins jtbl_apptltrs_mbrs to tbl_mbrs (jtbl_apptltrs_mbrs.LinkedMemberID = tbl_mbrs.MemberID).


MySQL: What is SQL profiler, does it different for MySQL?

The whole point about profiling is to ascertain where your system is spending time. This can often give developers some clues as to how to optimise their queries (by adding indexes for example).

The 600 pound gorilla in this area is Oracle's Wait Interface (OWI) which is one of the most sophisticated monitoring and profiling tools available to database professionals. MySQL has made strides in the last couple of years with the Perfomance Schema (commonly written P_S).

Again, the P_S allows the DBA/Developer to see what files are being accessed - and other information, i.e. where the system is spending time - knowing this can be very helpful for tuning or hardware purchasing decisions.

From here, MySQL's SHOW PROFILE command is now being deprecated in favour of the P_S.

I don't know the Neor Profile SQL 2 tool, but it looks interesting, although not Open Source :-(. There's also MySQL Workbench or Percona's tools. It's important to remember that none of these tools is a "silver bullet" - they just use the same information that's available in the P_S - they might present it nicely, but you're better off learning the P_S than a particular tool.

As for MySQL being different to Oracle (and to PostgreSQL, or Microsoft SQL Server, IBM DB2 or Firebird), then yes, there are differences between them that arise from the product's history but they all try and give some information to DBAs and Developers as to how the server is performing your query. Some more than others - Oracle's is very well developed, MySQL getting better (unsure of status of others). For the principles check out either Garcia-Molina or Lahdenmaki and Leach. You can then go to books on the particular server you are interested in/have to work with.


How to include the value of the query parameter in the email body using sp_send_dbmail

You can set the body of the email to be the query that you want and send the results as an attachment. For example:

DECLARE @sql_cmd NVARCHAR(max) =
'SELECT COUNT(*) FROM AdventureWorks2012.Production.WorkOrder
              WHERE DueDate > ''2004-04-30''
              AND  DATEDIFF(dd, ''2004-04-30'', DueDate) < 2';
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Adventure Works Administrator',
@recipients = 'danw@Adventure-Works.com',
@query =  @sql_cmd,
@body = @sql_cmd,
@subject = 'Work Order Count',
@attach_query_result_as_file = 1 ;

Sources:

http://msdn.microsoft.com/en-us/library/ms190307.aspx


Mysql Command required for getting values from my.cnf

You could use this reference: MySQL Documentation.

For example:

mysql> SHOW VARIABLES;
+---------------------------------+-------------------------------------+
| Variable_name                   | Value                               |
+---------------------------------+-------------------------------------+
| auto_increment_increment        | 1                                   |
| auto_increment_offset           | 1                                   |
| automatic_sp_privileges         | ON                                  |
| back_log                        | 50                                  |
…

  To obtain the row for a specific variable, use a LIKE clause as shown:

  SHOW VARIABLES LIKE '%size%';
  SHOW GLOBAL VARIABLES LIKE '%size%';


Why student attendance is not considered a fact in data warehousing?

Blame Kimball for the confusing term. Attendance is a fact (as in, "a thing that is known or proved to be true"). However, it is an event, and as such it does not have any extra information apart from references to the corresponding dimension keys. It probably should be called "measureless fact".

A sale, in addition to the dimension references, has measures: quantity and price.


Regardless of the quorum model, if a SQL Server 2012 secondary becomes disconnected, it will go offline.

This was changed in SQL Server 2014.

  When disconnected from the primary replica or during cluster quorum
  loss, readable secondary replicas now remain available for read
  workloads.

What's New for SQL Server 2014


postgresql - a local replication entry doesn't work

The problem is: "localhost" can be UNIX sockets, IPv4 or IPv6. The first rule you passed is for UNIX sockets. Try to add a rule for IPv4 and one for IPv6. It should work then.
Btw, a SIGHUP is enough in case you change pg_hba.conf.


Use an array returned from a subquery as argument in WHERE clause with ANY function in outer query

You're pretty close. The only thing you're missing is unnest() to turn the array into a set-valued function, allowing it to work on the right-hand-side of an ANY.

testdb=# create table person(favorite_number bigint);
CREATE TABLE
testdb=# create table event("when" date, hits bigint[]);
CREATE TABLE
testdb=# insert into event select '2018-01-23', '{2,3}';
INSERT 0 1
testdb=# insert into person select 2;
INSERT 0 1
testdb=# select * from person where favorite_number=ANY(select hit from event, unnest(hits) AS hit where "when"='2018-01-23');
 favorite_number
-----------------
               2
(1 row)


The database does not care about the IP addresses. Grid Infrastructure does. The method to change the IP addresses can be found in detail in the below MOS notes:

How to Modify Public Network Information including VIP in Oracle Clusterware (Doc ID 276434.1)

How to Modify Private Network Information in Oracle Clusterware (Doc ID 283684.1)


Simplest filesystem backup of Postgres database

Yes, base/ will likely be changed while the backup is occurring.  And yes, you need all of the WAL segments for the time between pg_start_backup() and pg_stop_backup().

Your existing filesystem backup tools do not provide you with the tools you need to back up a live database.  This is not unusual.  Rather than trying to fit a square peg into a round hole, why not just tell your filesystem backup tool to ignore the database, and then use a dedicated database backup tool to backup the database?  For example, use pg_basebackup in conjunction with WAL archiving, or pg_basebackup -X s as a standalone backup.  Of course, the files created as the result of this backup need to themselves backed up, but at that point they are "cold" files and your regular backup system will work.


MySQL - Multiple index with join and where query

WHERE table1.is_ignored = FALSE AND table1.state = 1

says to start a composite index with those two columns (in either order).  Then, looking at the rest of the SELECT, I see only sha256 being mentioned.  So, you may as make a "covering" index:

INDEX(is_ignored, state, sha256);

Meanwhile, get rid of id, assuming it is not really used elsewhere.  This will

shrink the disk footprint, thereby helping performance,
get rid of a unique index, thereby helping insert performance.

This leads to

PRIMARY KEY(sha256)

Meanwhile, table2 needs an index (possibly the PK) starting with sha256.

But... Digests (such as sha256) and UUIDs are terrible for performance due to their randomness.  Once the size of those two tables becomes more than your innodb_buffer_pool_size, performance will go downhill.  SSDs will help some.  More RAM will help a lot.  but you won't get much past 1K queries/sec.

More on building indexes.


High CPU in SQL Server is very often caused by poor indexing

Unfortunately, SQL Server 2000 lacks the tools of later versions to track these down easily

Saying that, if you run SQL Profiler you will be able to find high CPU queries and start looking at query plans to work out what indexes are missing


Capturing the Login Failure alerts specifc to bad password attempts

You should be able to setup an extended event and use either the generic error_reported event or process_login_finish (I'm not sure if process_login_finish is available in SQL Server 2014).

There's an article detailing a few different options at Using Extended Events to review SQL Server failed logins written in 2014 by Ivan Stankovic.


If you only need them to be an administrator of the database, you can grant them DBADM authority.

grant DBADM on database to (user or group) <name>

Set up an AIX or LDAP group for your admins and then assign them to that group. Then run the above command to assign the admin authority to that group.

Some further information for you:
If you have 9.7 or higher, then you can separate out other authorities such as granting DBADM WITH or WITHOUT DATAACCESS and WITH or WITHOUT ACCESSCTRL (WITH is the default). There is also the SECADM authority.

To grant everything under 9.7 or higher to mimic what you would have received in lower versions of DB2 then

grant DBADM,SECADM,DATAACCESS,ACCESSCTRL on database to (user or group) <name>

If you are talking about instance authorities, then you can add them to the groups that are assigned to the SYSADM,SYSCTRL,SYSMAINT, or SYSMON DB2 groups. (Although it sounds like you don't want that. I am merely mentioning it for holistics.)

For a good overview of DB2 priveleges, I would recommend checking out the Authorities Overview page in Information Center.


How can lots of ram (128 GB) be used to improve MySQL 5.5 index creation?

Your first concern is the data loading. Since you don't intend to have a primary key, but just an index, you should order your data before loading by the index column id1, in any UNIX flavor:

sort -n mytable.txt > mytable.sort

For loading the data, use either mysqlimport of LOAD DATA from the MySQL prompt. To speed up the loading, MYISAM tables benefit from increasing *bulk_insert_buffer_size*, *myisam_sort_buffer_size* or *key_buffer_size*, for INNODB tables, increase *innodb_buffer_pool_size* and *innodb_log_file_size*.

These increases might not be sufficient, and the loading still might slow down as the memory fills up. In that case, it is more efficient to load the data in chunks. You can monitor the speed by executing an hourly du in the mysql root directory:

#!/bin/bash
while [ 1 ]
do
    du -hs database/.
    sleep 3600
done

Just for completeness (not relevant for this specific case): Disabling indexes also helps speedup the loading process.

Now to querying:
This is a bit more complex, as it depends on your data, and the way you want to query your table. Biggest factors that influence performance are:

buffers: set by variables. Execute your typical queries and compare
the "show variables" and "show status" output to identify
bottlenecks.
indexes: you already covered that
joins: Depending on the way you join with other tables, it might be
better to denormalize your table(s) in a data warehouse style,
especially if you want to join two large tables.

Great articles on data loading and large table query optimization can be found at:
http://www.mysqlperformanceblog.com/2007/05/24/predicting-how-long-data-load-would-take/
http://www.mysqlperformanceblog.com/2006/06/09/why-mysql-could-be-slow-with-large-tables/

Review the default and allowed values for these and other variables at:
http://dev.mysql.com/doc/refman/5.5/en/server-system-variables.html


Is a mySQL or mongoDB relational database scalable for holding 80 GBs of additional logs per day?

MySQL can handle at least some aspects of such.

At 15TB, disk I/O is the number one problem!  Both on INSERT and SELECT.

Indexes are BTree structured.  This gives "close to instantaneous" fetching of one row, given the index.  It also gives rapid scanning in the BTree order, forward or backward.

You can have multiple unrelated indexes, but it is problematical.  Indexes are kept up to date as rows are inserted.  This is not the least bit of a problem if the indexes fit in cache.  And not much of a problem when the indexes are somewhat bigger than cache.  The problem comes when an index is much bigger than can be cached in RAM, and the next insert needs to update some spot in the BTree that is not currently cached.  INSERTing slows to disk speed -- perhaps 10s of millions of rows inserted per day.  (This is regardless of LOAD DATA, batched INSERTs, staging tables, etc.)

So, you can't have all the indexes you may want.  And without indexes, forget about "close to instantaneous".

So, what to do?  I suggest you turn the design around -- What do you want out of this project?  That is, put aside the discussion of size and storage; instead think about what SELECTs you would like.  Then think about how to arrange the data to minimize I/O.  Would you like to present some of the SELECTs for discussion?

I will probably push you toward summarizing the data as it comes in, storing the summaries in tables, and tossing the raw data (or at least not storing it in a database).

Two further notes:

Tokudb is excellent at ingesting indexed data -- it can generally do it faster than the standard "engines" in MyISAM.  MariaDB has Tokudb built in.

For purging the data, see my blog on such.


Adding a Clustered index to a HEAP table on someone else's app

I'm going to go on the assumption that you've identified a problem that needs to be fixed... though I'm not entirely convinced of that yet.

Similar to my answer here, there are two cases when it comes to vendor applications.

The application is currently under license and/or the vendor/owner doesn't want you meddling.

In this case, definitely do not change the schema in any way, including adding indexes of any kind.

Making schema changes may in fact void your license contract, but this can also cause problems for the vendor if they decide to fix the issues in a future schema update. Speaking from personal experience from the point of view of the vendor, we sometimes have to very actively tell clients to stop creating objects in our databases, because it impacts our ability to successfully apply schema changes. (I'm not just saying that -- we had a case a couple months ago where a schema update failed for one client because of an unexpected dependency from a client-created object. Slightly different scenario than adding an index, but still, it does come up.)

There are rare cases where database-level changes are okay, but you need to make absolutely certain the vendor is okay with what you're planning to do.

The best option for this scenario is... actually, not to make any changes yourself. Put together a compelling case to prove why it's important these changes be made, and present it to the vendor for them to implement in a future release.

From a vendor's point of view, ideally suggestions are specific, simple to implement and test, and have a high positive impact for all clients. In a large application, suggesting that all tables that are heaps be converted to clustered indexes may be (and probably is) a good idea, but it's a complete non-starter. Instead, figure out the top 5-10 most important tables or 2-3 application areas that would benefit from this type of change in your environment.
The application is out of license or the vendor/owner doesn't care what you do with it.

In this case, you can add any index you want, but objects should remain in place with the same names (note I didn't say the same objects). There are many options here, depending on how the application and database works internally. If you still plan to apply future vendor-supplied schema changes, tread very carefully and make sure you create undo scripts for all your changes.

The key to successfully making changes is to maintain application functionality... easier said than done, of course, if you plan to make significant changes.

That said, simply adding clustered indexes is pretty safe. The challenge will be selecting an appropriate key for each index, and this should be done by hand, not automated. The only way to do this part well is by familiarizing yourself with a table's access patterns and purpose.

Come to think of it, you could go through the same process as in the other case: identify the 5-10 tables that would have the highest overall impact, and fix those first. You may find that's all that needs to be done.

Edit: this answer, in a more general sense, is now available in video form.


Moving SQL Server 2012 Database to SQL Server 2008 R2

Script out schema using SSMS GUI. And using this script create the database on the destination server.
Run below script on the source Server in SSMS with Text output CTRL+T:

select '"C:\Program Files\Microsoft SQL Server\100\Tools\Binn\bcp.exe" '-- path to BCP.exe
        +  QUOTENAME(DB_NAME())+ '.'                                    -- Current Database
        +  QUOTENAME(SCHEMA_NAME(SCHEMA_ID))+'.'
        +  QUOTENAME(name)
        +  ' out D:\BCP_OUT\'                                           -- Path where BCP out files will be stored
        +  REPLACE(SCHEMA_NAME(schema_id),' ','') + '_'
        +  REPLACE(name,' ','')
        + '.dat -T -E -SSERVERNAME\INSTANCE -n'                         -- ServerName, -E will take care of Identity, -n is for Native Format
from sys.tables
where is_ms_shipped = 0 and name <> 'sysdiagrams'                       -- sysdiagrams is classified my MS as UserTable and we dont want it
---and schema_name(schema_id) <> 'some_schema_exclude'                     -- Optional to exclude any schema
order by schema_name(schema_id)

In above script change the bcp.exe path to match. I have commented out the schema part in above script as you want to migrate all data from all schemas.

Once you have the script output in TEXT format, open a notepad and paste it in and save it as .BAT file.

Once saved, run the .BAT file by launching the CMD prompt. This will produce all the .dat files in the folder D:\BCP_OUT\ <-- change this as per your location.
Now you will have all the .dat files. These are the BCP OUT files with data. So run the below script on the destination server. You can copy all the bcp files to the destination server or just use SSMS from the source server to generate the scripts.Make sure you change the bcp.exe path and the destination_database_Name.

--- Execute this on the destination server.database from SSMS.
--- Make sure the change the @Destdbname and the bcp out path as per your environment.

declare @Destdbname sysname
set @Destdbname = 'destination_database_Name'               -- Destination Database Name where you want to Bulk Insert in
select 'BULK INSERT '                                       -- Remember Tables **must** be present on destination Database
        +  QUOTENAME(@Destdbname)+ '.'
        +  QUOTENAME(SCHEMA_NAME(SCHEMA_ID))+'.'
        +  QUOTENAME(name)
        + ' from ''D:\BCP_OUT\'                             -- Change here for bcp out path
        +  REPLACE(SCHEMA_NAME(schema_id),' ','') + '_'
        +  REPLACE(name,' ','')
        +'.dat''
        with (
        KEEPIDENTITY,
        DATAFILETYPE = ''native'',
        TABLOCK
        )'  + char(10)
        + 'print ''Bulk insert for '+REPLACE(SCHEMA_NAME(schema_id),' ','') + '_'+  REPLACE(name,' ','')+' is done... '''+ char(10)+'go'
from sys.tables
where is_ms_shipped = 0 and name <> 'sysdiagrams'           -- sysdiagrams is classified my MS as UserTable and we dont want it
--and schema_name(schema_id) <> 'some_schema_exclude'         -- Optional to exclude any schema
order by schema_name(schema_id)

Above will generate scripts for bulk insert using SSMS set in TEXT MODE as output. Copy the generated script and run it in another window using SSMS.

Hope that helps.


How can I query for terms like "@foo" with Postgres full text search?

The problem is the default text parser, in that it splits out the '@' symbol

You can see it here:

select
  ts_lexize('simple', '@foo'), -- {@foo}
  ts_parse('default', '@foo'); -- { (12,@), (1,foo) }

lexize is fine and keeps the @, but the parser splits it out

You'd need to write your own parser or change the source, which isn't fun if you don't know C!

One work-around would be to change @foo to something like ATSYMBOLfoo using some wrapper function for to_tsvector and plainto_tsquery


Here is an example of building a dynamic xp_cmdshell command that includes a timestamp on the file

declare @Cmd varchar(100)
set @Cmd = 'copy C:\test\a23.txt C:\test\a23-' + replace(replace(convert(varchar(19),sysdatetime()),' ','-'),':','-') + '.txt'
print @cmd
exec xp_cmdshell @cmd;


How does shrinking a SQL Server log file affect performance?

I really recommend reading Importance of proper transaction log size management by Paul S. Randal.

The quintessence is that there are only two really good ways to do transaction log handling:

Either go with regular LOG file backups and the LOG-file will reuse it's space after each LOG backup and won't grow indefinitely, or
Use the SIMPLE recovery model and you don't have to care about your LOG-file size, as you do regular full backups.

What concerns LOG-file truncation and performance is that you will always get a performance hit when the LOG file is to be increased (a quote from the above-linked blog post):

  If you shrink the log, then it's going to grow again - possibly causing VLF fragmentation, and definitely causing your workload to pause while the log grows, as the log can't use instant initialization [...]

Update: Don't mistake LOG file truncation for DATA file shrinking. DATA file shrinking is really bad. See Why you should not shrink your data files for details.


It would be great to know how many connections you spect to handle at the same time.

I would size this depending on your server hardware use expectations

If you expect 1024 concurrent connections as much this setting is correct.

max_client_conn = 1024

But here are the changes that worked for me. If you change the pooling mode you can reduce the default_pool_size, this increased my performance

pool_mode = transaction
default_pool_size = 50

Take a look at the different pooling modes to see if you can benefit from a more efficient pooling mode than the default value session pool mode.

  Session pooling
  Most polite method. When client connects, a server connection will be assigned to it for the whole duration it stays connected. When client disconnects, the server connection will be put back into pool. This mode supports all PostgeSQL features.

  Transaction pooling Server connection is assigned to client only
  during a transaction. When PgBouncer notices that transaction is over,
  the server will be put back into pool.
  This mode breaks few session-based features of PostgreSQL. You can use
  it only when application cooperates by not using features that break.
  See the table below for incompatible features.

  Statement pooling Most
  aggressive method. This is transaction pooling with a twist -
  multi-statement transactions are disallowed. This is meant to enforce
  "autocommit" mode on client, mostly targeted for PL/Proxy.


There is no inconsistency here, this is simply due to how you have the volumes mounted and where you have located you MongoDB database path.  You have one logical volume mounted on root (/) and the other mounted on /home - I am guessing you do not have your database path in MongoDB set to something under /home instead it is set to something else which is under the root path.

The most common database paths for MongoDB are /data/db (the default) and /var/lib/mongodb (default on Ubuntu for example), but you can set it to whatever you want.  Given how you have your mount points configured, both of those common defaults would go under the root mount point and hence onto the /dev/mapper/vg_db1-lv_root volume.  If you switch your database path to be under /home or you re-do your mount points appropriately the data will then be placed on the second volume.


EC2 - How to correctly back up PostgreSQL data?

See the fine manual. If my advice conflicts with its' in any way, it's right.

A sync isn't a bad idea, unless your copy tool fsync()s each WAL file it writes and the directory it's in before copying the next one. An incomplete last WAL file doesn't matter much; at worst, you just delete it. Pg will generally choke on an incomplete WAL - though there's no checksumming done, so you could be really unlucky and have it try to apply garbage data that by sheer insane chance happened to look like real WAL records. In your position I'd be syncing the volume before a snapshot to make sure any unwritten dirty buffers in RAM hit the file system image on disk. A freeze would help avoid messy but non-fatal partially written WALs, so it's not a terrible idea but not vital.  What's vital is to have an undamaged timeline up until the point of recovery. Personally, I write my WALs to a temporary file name and rename them to their final name only once fully copied; if you do this, you don't need to freeze.
Sounds correct. A live snapshot is just like doing a plug pull test on a live system with write-through caching. Your database should recover fine when restored from a live snapshot, same as after plug-pull. I'd recommend that you automate tests of restores from snapshots. (Note: A snapshot restore test is not a complete substitute for plug pull testing because it doesn't account for possible disk, raid controller, etc write caching). Not only the config files and the dump, but the database its self should be fine after your snapshot. Consider syncing the volume before the snapshot to make sure all the dump data etc has actually hit disk.
2a. Might save some disk space. Little difference otherwise. You'll get to keep the snapshots a lot longer without all the churn of the live database on them.
Why even snapshot your code volume? A plain file level copy may well be just fine. Certainly a live snapshot should be.
This is not a solid backup scheme. It fails in one critical area: There is no restore testing and validation being performed. You should always test your backups on a regular basis to make sure you can really restore them.

Personally, I recommend that you use WAL shipping, or send database dumps, to a different host, preferably one not on Amazon EC2 or at least in a different region. This host should perform automated restore tests, send reports to you of the results, and should also be checked manually.

While your snapshots (containing dumps) will be on S3, and will be safe there, that doesn't mean they'll be accessible when you need them urgently. Amazon's durability claims are reassuring, but your data can still be safe and completely inaccessible to you during a badly timed outage of the S3 service.


Why some of mysql system variables are readonly

innodb_buffer_pool_size
The InnoDB Storage Engine has too many moving parts to just allow the buffer pool to be resized on the fly because the buffer pool interacts with system tablespace (ibdata1) and the transactions logs (ib_logfile0, ib_logfile1).
log-bin
In spite of the MySQL Documentation, you can change the effect of log-bin using another variable called SQL_LOG_BIN. Look at the effects:
SESSION
If you run
SET SQL_LOG_BIN = 0;

this will disable your DB Session from recording binlog events.
If you run
SET GLOBAL SQL_LOG_BIN = 0;

this will disable all incoming DB Sessions from recording binlog events.
General Log / Slow Log
As a DBA, I am very glad that the general log and slow log are dynamic. Otherwise

how could do perform any log rotation ?
how to stop these logs before diskspace runs out ?

Here are some posts I made about doing log rotations

Nov 10, 2012 : Redirecting MySQL errors and slow logs into another database
Mar 30, 2012 : MySQL slow log configuration
Jun 30, 2011 : How do I output MySQL logs to syslog?

Datetime Format
mysqld is usually cognizant of timezone and internalization of datetime displays. I do not see a beneficial reason for shifting the datetime format on demand. This may actual cause problems, not so much with mysqld, but with PHP/Python/Ruby scripts that read dates from the MySQL instance and may misinterpret '04/12/2013' as April 12th when it should be Dec 4th. That would be developer's nightmare to deal with. Having datetime format a read-only variable simply provides a safety net against such a death-defying move whereas proper planning of the database and code interoperability with dates would be in order.


Is there a way to construct a PIVOT's IN clause using a SELECT?

The T-SQL documentation of the FROM clause has this for the  specification:

<pivoted_table> ::=
    table_source PIVOT <pivot_clause> [ [ AS ] table_alias ]

<pivot_clause> ::=
        ( aggregate_function ( value_column [ [ , ]...n ])
        FOR pivot_column
        IN ( <column_list> )
    )

<column_list> ::=
    column_name [ ,...n ]

So no, it doesn't allow you to select the column names with a SELECT statement, and yes, you have to "hard-code" the PIVOT column names.


SQL Server 2014 only using 10 percent of its available memory?

if you what that sql server reservs an amount of ram you have to configure minimum server memory.
In your case the min server memory is zero; then sql server takes only the ram it nees to answer the users' queries.
Change it in this way:
EXEC sp_configure N'min server memory (MB)', MBs_you_like
RECONFIGURE


Trying to trace error in Stored Procedure using XEvents

You can configure an extended event session with an event:

error_reported: This event gets fired every time that an error happens in the server.

For your particular case you are able to filter events for message_id = 248.

SELECT text FROM sys.messages WHERE message_id = 248

  The conversion of the %ls value '%.*ls' overflowed an int column.

That event contains a field tsql_frame which looks like one below (just an example):

<frame level="2" handle="0x03000500A711127995BB6600B4AA000001000000000000000000000000000000000000000000000000000000" line="83" offsetStart="2634" offsetEnd="3288" />

So, you got sql_handle plus start and end offsets of the statement you are looking for. And getting the statement (values are from the example above):

SELECT
    CASE
        WHEN s.offsetStart > 0 THEN
            CASE s.offsetEnd
               WHEN -1 THEN
                  SUBSTRING(t.TEXT, (s.offsetStart/2) + 1, 2147483647)
               ELSE
                  SUBSTRING(t.TEXT, (s.offsetStart/2) + 1, (s.offsetEnd - s.offsetStart)/2+1)
            END
        ELSE
            CASE s.offsetEnd
               WHEN -1 THEN
                  RTRIM(LTRIM(t.[text]))
               ELSE
                  LEFT(t.TEXT, (s.offsetEnd/2) +1)
            END
        END AS [executing statement]
FROM sys.dm_exec_sql_text(0x03000500A711127995BB6600B4AA000001000000000000000000000000000000000000000000000000000000) t
CROSS JOIN (VALUES (2634, 3288) ) s(offsetStart, offsetEnd)


How to check what Database Engine is installed on the DataBase server that I have acces to run queries on?

I assume that your web interface lets you issue SQL commands. If so, you can use:

SELECT version();

PostgreSQL

If you are on a PostgreSQL database, you get a response similar to

PostgreSQL 9.6.1 on x86_64-apple-darwin14.5.0, compiled by Apple LLVM version 7.0.0 (clang-700.1.76), 64-bit

MySQL

If you are on a MySQL database, the answer looks like

5.7.12-log

Oracle

If you are on an Oracle database, you'll get an error message:

ORA-00923: FROM keyword not found where expected

(The ORA-xxxx tells you you're on Oracle). To find out which specific version, try:

SELECT banner as "oracle version" from v$version

You'll get a response like:

Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE    11.2.0.2.0  Production
TNS for 64-bit Windows: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production

Microsoft SQL Server

If you're on MS SQL Server, the response will also be an error, and look like:

'version' is not a recognized built-in function name.

In that case, you can try:

SELECT @@version ;

And you'll get, as a response, something looking like:

Microsoft SQL Server 2016 (SP1) (KB3182545) - 13.0.4001.0 (X64)
    Oct 28 2016 18:17:30
    Copyright (c) Microsoft Corporation
    Enterprise Edition (64-bit) on Windows Server 2012 R2 Standard 6.3 <X64> (Build 9600: ) (Hypervisor)

SQLite

If you're on a SQLite database, you'll get an error message when you try SELECT version():

 could not prepare statement (1 no such function: version)

In that case, you can try:

 SELECT sqlite_version()

And the response will look like:

3.14.0


Can an SSIS variable have a default value?

you should be able to do it quite easily like this:

Bring the variables editor up & add a variable to contain your preferred date
Inspect the variable property window
Choose the ellipsis for the Expressions property to get the expression editor
Define your date output using getdate() functions etc. (reasonable subset of T-SQL fns are present)
The result will be shown in the variables pane (value) & re-evaluated at runtime
For testing, remove the expression & replace with a fixed value of your choosing
Get really clever & add separate debug_date & debug_mode variables that you can use in the expression to switch between the expression & your fixed value depending which mode you are in.


Index fragmentation increased significantly after rebuild

Just like @KookieMonster noticed, You have Auto Shrink turned on. And one of disadvantages of shrink commands is fragmenting Your indexes once again:

http://www.sqlskills.com/blogs/paul/why-you-should-not-shrink-your-data-files/


Setting up a listener for sql server alwayson availability groups without computer object rights in the AD

There is one proper way to pre-stage the listener and one way to allow the cluster to create the listener itself. Please note that YOUR account is not what is used to authorize to AD to create the listener when creating it through FCM/Powershell or SQL Server, the CNO is used as security context.

The official pre-stage way

Find the OU with the CNO in it
Create a computer object, name it your DNS listener name
Set the security on the new VCO and give the CNO full control over it (full isn't required but the list of needs is fairly long so just give it full control)
Create the listener through SSMS/TSQL/Powershell

The official automatic creation way

Give the CNO Create computer objects, list properties, read properties, write properties over the OU it resides in.
Create the listener through SSMS/TSQL/Powershell

They way that is listed in the other answer given is a mix of the pre-staging way with how to create multiple listeners. Additionally it has improper configuration when it comes to the security and will most likely come back to bite you if you do it that way.


SQL Server query takes too long to execute. What could the problem be?

Thank you for your effort and suggestions. Here is the solution which took only 6 minutes to execute:

WITH T1
AS (
    SELECT FileCode
        ,dbo.GetLastDayOfMonth(left(f.FileFormationDate, 4), substring(f.FileFormationDate, 6, 2)) AS Datekey
    FROM [StageArea].[Sales].[viwLossCalculation] f
    GROUP BY FileCode
        ,dbo.GetLastDayOfMonth(left(f.FileFormationDate, 4), substring(f.FileFormationDate, 6, 2))
    )
    ,T2
AS (
    SELECT dbo.GetLastDayOfMonth(c.Year, c.Monthkey) AS datekey2
        ,ComplaintCount AS complaintcount
    FROM [StageArea].[Preservation].[viwComplaint] c
    )
SELECT C.DateKey
    ,f.complaintcount
    ,COUNT(DISTINCT c.FileCode) AS FileCodeCount
FROM T1 C
INNER JOIN T2 F ON C.DateKey = F.DateKey2
GROUP BY C.DateKey
    ,f.ComplaintCount;


What's the term for a schema that has field+value column rather than individual columns?

AFAIK it's referred to as the Entity-Attribute-Value (EAV) model


Is there a way to show index_advantage more friendly?

As Michael Green noted, this doesn't trivially convert into a percentage. This query looks at the top 25 "scores", based on this formula; but those scores aren't based some known maximum.

If you looked at all the scores, they'd be based on query activity where SQL Server thinks an index that might help, not all SQL activity. And, of course, the numbers are much lower when you're server's been up for 1 day vs. 1 month.

Ultimately, I believe these scores are intended to give you a basis for comparison; a query scoring 2000000 is more likely worthwhile to look at than one scoring 200. - RDFozz


Database Design - Creating Multiple databases to avoid the headache of limit on table size

In my answer to your previous question, I mentioned how the size of an individual table is 2TB when you use innodb_file_per_table. There may be a way to surpass the table limit by doing just the the opposite, leaving innodb_file_per_table disabled.
With innodb_file_per_table disabled, there are three ways you can go about this:
TECHNIQUE #1 : Use multiple system tablespaces in ext3
You could chain InnoDB tablespaces 2TB at a time. Start off with this
innodb_data_file_path=ibdata1:10M:autoextend:max:2048G

When ibdata1 is about to hit 2TB, add another ibdata file
innodb_data_file_path=ibdata1:2048G:ibdata2:10M:autoextend:max:2048G

When ibdata2 is about to hit 2TB, add another ibdata file
innodb_data_file_path=ibdata:2048G:ibdata2:2048G:ibdata3:10M:autoextend:max:2048G

and so on...
UPDATE 2013-06-01 20:45 EDT
I just discovered something with reference to setting the max filesize for a system tablespace. Here is the situation:
Client has the following InnoDB files
[root@l*****]# ls -l ibd*
-rw-rw---- 1 s-em7-mysql s-em7-mysql     362807296 Jun  2 00:15 ibdata1
-rw-rw---- 1 s-em7-mysql s-em7-mysql 2196875759616 Jun  2 00:15 ibdata2

and has this setting
innodb_data_file_path=ibdata1:346M;ibdata2:500M:autoextend:max:10240000M

MySQL was reporting table full errors.
I tried creating a new file ibdata3 with
innodb_data_file_path=ibdata1:346M;ibdata2:2048G;ibdata3:10M:autoextend

That value did not work
I tried using new sizes for the max value

2046G
2045G
2046000M

After trying for 15 min with different combinations, none of these values worked. Looks like mysqld was never going to startup again. I decided to try the exact number of bytes for the filesize of ibdata2:
innodb_data_file_path=ibdata1:346M;ibdata2:2196875759616;ibdata3:10M:autoextend

When I started mysql, LO AND BEHOLD, MySQL was up.

ibdata3 was created at 10M
ibdata3 was soon growing in 8M chunks
Client was happy once again

Moral of the story : At times, you may need to use the exact filesize for a system tablespace in innodb_data_file_path
TECHNIQUE #2 : RAW DISK PARTITION
According to MySQL 5.0 Certification Study Guide, Page 428

Any raw partitions in the configuration must exist but must have the modifier newraw listed after the size of the file specification. newraw tells InnoDB to initialize the partition when the server starts up. New partitions are treated as read-only after initialization. After InnoDB initializes the tablespace, stop the server change newraw to raw in the partition specfication, and restart the server. For example, to use a 10GB Unix partition named /dev/hdc6, begin with a configuration like this:

[mysqld]
innodb_data_home_dir=
innodb_data_file_path=/dev/hdc6:10Gnewraw

Start the server and let InnoDB initialize the tablespace. Then stop the server and change the configuration from newraw to raw:

[mysqld]
innodb_data_home_dir=
innodb_data_file_path=/dev/hdc6:10Graw

After changing the configuration, restart the server.

Just create a RAID10 Disk with whatever size you need. Just don't put any filesystem on it.
TECHNIQUE #3 : Use ext4 with TECHNIQUE #1
You could chain InnoDB tablespaces 16TB at a time. Start off with this
innodb_data_file_path=ibdata1:10M:autoextend:max:16384G

When ibdata1 is about to hit 16TB, add another ibdata file
innodb_data_file_path=ibdata1:16384G:ibdata2:10M:autoextend:max:16384G

When ibdata2 is about to hit 16TB, add another ibdata file
innodb_data_file_path=ibdata:16384G:ibdata2:16384G:ibdata3:10M:autoextend:max:16384G

And so on...


Can you get multiple values from a table into one row?

While it is possible to have a single row for Kingsway and a column for each facility it would be a poor design.

Better to have one table to list clubs. A second table holds the superset of all possible facilities across all clubs. A third table shows which facilities each club actually has. It will have a row for each (club, facility) pair e.g. (Kingsway, pool), (Kingsway, bicycles), (Kingsway, sauna) etc. If another club also had the same facilities there would be a further three rows in this table: (Club2, pool), (Club2, bicycles), (Club2, sauna).

How these tables become populated depends on the source of the data and the particular database design chosen. If the data comes from off-line sources, say brochures, there's no alternative but to type it in to each of the three tables. If the source is digital it could be loaded as-is into a disposable staging table. The appropriate parts are then copied to each of the three data tables. Finally the staging table can be dropped.

The final design could deploy either natural keys or surrogate keys. Using natural keys the third, linking table will appear exactly as shown above. The actual string "Kingsway" and the actual string "pool" will be in the linking table. Using surrogate keys the club and facility tables must be loaded first to creating the pairings between values in columns club_name and club_id, and between facility_name and facility_id. Then the linking table can be populated with the correct (club_id, facility_id) pairs. Likely this will need to join the staging table to the club and facility tables.


select rows where timestamp is a multiple of n seconds

something like

select * from table where MOD(UNIX_TIMESTAMP(timestamp),300)) = 0;

should do the job. But I am not sure if this is correct. The idea is to try to modulo divide the minute part evenly. Be aware, that this may cause in a full table scan


Since the databases in an AG always have to have Full Recovery Model, yes, the databases definitely need log backups scheduled frequently to avoid excessive log file growth.


You mention splitting at the middle, but your requirement is adding a boundary to the beginning (before the first existing boundary). Consequently, no costly data movement will be needed as long as no existing values are less than or equal to 20140100. I'll assume that is the case here.

Since the first partition is on the wrong filegroup (PRIMARY), first move it to the proper monthly filegroup:

--move first partition to proper filegroup
ALTER PARTITION FUNCTION [pf_monthly_dateid]()
    MERGE RANGE(20140100);
ALTER PARTITION SCHEME [ps_monthly_dateid]
    NEXT USED [201312];
ALTER PARTITION FUNCTION [pf_monthly_dateid]()
    SPLIT RANGE(20140100);

Then, create the needed historical partitions before loading data:

ALTER PARTITION SCHEME [ps_monthly_dateid]
    NEXT USED [201311];
ALTER PARTITION FUNCTION [pf_monthly_dateid]()
    SPLIT RANGE(20131200);
ALTER PARTITION SCHEME [ps_monthly_dateid]
    NEXT USED [201310];
ALTER PARTITION FUNCTION [pf_monthly_dateid]()
    SPLIT RANGE(20131100);
ALTER PARTITION SCHEME [ps_monthly_dateid]
    NEXT USED [201309];
ALTER PARTITION FUNCTION [pf_monthly_dateid]()
    SPLIT RANGE(20131000);
...

The general best practice is to plan such than only empty partitions are split. Data movement performed during SPLIT and MERGE to changed boundaries requires about 4 times logging as normal DML. So also consider creating an empty partition on the proper filegroup (even if no files) in anticipation of loading prior data.


For a better analysis please provide a network dump (wireshark) from the client.
A typical problem with vpn is ip fragmentation. This would explain the established state in netstat and that it is working with another client (which might using smaller packets). But without trace it is just a wild guess.


Yes, you'd add all three columns. Assuming they have the same names in both tables, you'd use something like

foreign key (Name, BoughtFrom, TimeBought)
  references the_other_table_name (Name, BoughtFrom, TimeBought)

If you decide to use a surrogate ID number, you'll still need a unique constraint on {Name, BoughtFrom, TimeBought}.  You can do that with something along these lines.

create table your_table (
  your_id_number integer primary key,
  Name ... ,
  BoughtFrom ... ,
  TimeBought ... ,
  unique (Name, BoughtFrom, TimeBought)
);

The surrogate key doesn't have to be an ID number. It doesn't have to be any kind of number. But an automatically incrementing ID number is the most common.

Surrogate means takes the place of. A surrogate key, like an ID number, takes the place of a natural key. Kind of like a surrogate mother takes the place of a natural mother.


Possible to tell what a query was in session I just killed?

By default nothing keep tracks of all running query in SQL Server.  Depending on what the query was you might explore few things. None of these guarantee that you will find what you are looking for.

sys.dm_exec_query_stats (Transact-SQL)

Returns aggregate performance statistics for cached query plans in SQL Server. The view contains one row per query statement within the cached plan, and the lifetime of the rows are tied to the plan itself. When a plan is removed from the cache, the corresponding rows are eliminated from this view.

You will need to join these with other DMVs. Link has examples.
A Few Cool Things You Can Identify Using the Default Trace by Greg Larsen
Use the system_health Session if you still have the trace files.
Since you mentioned SQL Server not responding I would also look at SQL Error log, Windows event logs.


Renaming a table in MySQL does not require a temporary table.
There are two statements that perform an equivalent operation.
RENAME TABLE t1 TO t2;
ALTER TABLE t1 RENAME TO t2; # as long as no other options to ALTER are also specified

Renaming a table acquires a metadata lock on the table which requires that no statements be running against the table, no transactions have row locks, and no transactions are holding on to consistent snapshots of any MVCC versions of the table.
Either style of RENAME operation will attempt to acquire the metadata lock and subsequently-started statement impacting the table will block, waiting for the pending metadata lock.

If you use ALTER TABLE tbl_name RENAME TO new_tbl_name without any other options, MySQL simply renames any files that correspond to the table tbl_name without making a copy.
— http://dev.mysql.com/doc/refman/5.0/en/alter-table.html

Note that's a link to the 5.0 documentation, and the 5.1, 5.5, 5.6, and 5.7 all have the same information.
The major delay you could see will come from other sessions preventing the RENAME from starting because the locks can't be immediately required.
If you are able to execute a FLUSH TABLES table_name; statement and have it return within a few seconds, that should serve as confirmation that the rename operation will complete in a similar amount of time, since it will need the same locks in order to remove any entries in the query cache related to the table, close the table, and clean up some internal structures like the table share object... the table is automatically reopened by the next statement that accesses it... so it gives you a good test of what to expect and would be a good idea to run first, since it's typically going to make the actual rename operation slightly shorter by leaving less work for the rename to do.
SHOW FULL PROCESSLIST; while waiting for the FLUSH to complete (if there's a delay) will give you an idea of things that might get in the way of the rename operation, and you can safely KILL the flush if you need to.


Postgresql: How to add a role that inherits from another?

CREATE ROLE doe LOGIN INHERIT;
CREATE ROLE sales NOINHERIT;
CREATE ROLE marketing NOINHERIT;
GRANT sales to doe;
GRANT marketing to sales;

If you connect to PostgreSQL as doe, you will have privileges of doe plus privileges granted to sales, because doe user role has INHERIT attribute. However, you do not have privileges of marketing because the NOINHERIT attribute is defined for the sales user role.
Note:
The role attributes LOGIN, SUPERUSER, CREATEDB, and CREATEROLE can be thought of as special privileges, but they are never inherited as ordinary privileges on database objects are.


How would I track all price changes in a db in order to get the price of 'x' product on 'y' date

If I understand the scenario appropriately, you should define a table that retains a Price time series; therefore, I agree, this has a lot to do with the temporal aspect of the database you are working with.
Business rules
Let us start analyzing the situation from the conceptual level. So, if, in your business domain,

a Product is purchased at one-to-many Prices,
each Price of purchase becomes Current at an exact StartDate, and
the Price EndDate (which indicates the Date when a Price ceases to be Current) is equal to the StartDate of the immediately subsequent Price,

then that means that

there are no Gaps between the distinct Periods during which the Prices are Current (the time series is continuous or conjunct), and
the EndDate of a Price is a derivable datum.

The IDEF1X diagram shown in Figure 1, although highly simplified, depicts such a scenario:

Expository logical layout
And the following SQL-DDL logical-level design, based on said IDEF1X diagram, illustrates a feasible approach that you can adapt to your own exact needs:
-- At the physical level, you should define a convenient
-- indexing strategy based on the data manipulation tendencies
-- so that you can supply an optimal execution speed of the
-- queries declared at the logical level; thus, some testing
-- sessions with considerable data load should be carried out.

CREATE TABLE Product (
    ProductNumber INT      NOT NULL,
    Etcetera      CHAR(30) NOT NULL,
    --
    CONSTRAINT Product_PK PRIMARY KEY (ProductNumber)
);

CREATE TABLE Price (
    ProductNumber INT  NOT NULL,
    StartDate     DATE NOT NULL,
    Amount        INT  NOT NULL, -- Retains the amount in cents, but there are other options regarding the type of use.
    --
    CONSTRAINT Price_PK            PRIMARY KEY (ProductNumber, StartDate),
    CONSTRAINT Price_to_Product_FK FOREIGN KEY (ProductNumber)
        REFERENCES Product (ProductNumber),
    CONSTRAINT AmountIsValid_CK    CHECK       (Amount >= 0)
);

The Price table has a composite PRIMARY KEY made up of two columns, i.e., ProductNumber (constrained, in turn, as a FOREIGN KEY that makes a reference to Product.ProductNumber) and StartDate (pointing out the particular Date in which a certain Product was purchased at a specific Price).
In case that Products are purchased at different Prices during the same Day, instead of the StartDate column, you may include one labeled as StartDateTime that keeps the Instant when a given Product was purchased at an exact Price. The PRIMARY KEY would then have to be declared as (ProductNumber, StartDateTime).
As demonstrated, the aforementioned table is an ordinary one, because you can declare SELECT, INSERT, UPDATE and DELETE operations to manipulate its data directly, hence it (a) permits avoiding the installation of additional components and (b) can be used in all the major SQL platforms with some few adjustments, if necessitated.
Data manipulation samples
To exemplify some manipulation operations that appear useful, let us say that you have INSERTed the following data in the Product and Price tables, respectively:
INSERT INTO Product
    (ProductNumber, Etcetera)
VALUES
    (1750, 'Price time series sample');

INSERT INTO Price
    (ProductNumber, StartDate, Amount)
VALUES
    (1750, '20170601', 1000),
    (1750, '20170603', 3000),
    (1750, '20170605', 4000),
    (1750, '20170607', 3000);

Since the Price.EndDate is a derivable data point, then you must to obtain it via, precisely, a derived table that can be created as a view in order to produce the “full” time series, as exemplified below:
CREATE VIEW PriceWithEndDate AS

    SELECT  P.ProductNumber,
            P.Etcetera AS ProductEtcetera,
           PR.Amount   AS PriceAmount,
           PR.StartDate,
           (
                SELECT MIN(StartDate)
                      FROM Price InnerPR
                     WHERE P.ProductNumber   = InnerPR.ProductNumber
                       AND InnerPR.StartDate > PR.StartDate
           ) AS EndDate
        FROM Product P
        JOIN Price   PR
          ON P.ProductNumber = PR.ProductNumber;

Then the following operation that SELECTs directly from that view
  SELECT ProductNumber,
         ProductEtcetera,
         PriceAmount,
         StartDate,
         EndDate
    FROM PriceWithEndDate
ORDER BY StartDate DESC;

supplies the next result set:
ProductNumber  ProductEtcetera     PriceAmount  StartDate   EndDate
-------------  ------------------  -----------  ----------  ----------
         1750  Price time series…         4000  2017-06-07  NULL      -- (*)
         1750  Price time series…         3000  2017-06-05  2017-06-07
         1750  Price time series…         2000  2017-06-03  2017-06-05
         1750  Price time series…         1000  2017-06-01  2017-06-03

-- (*) A ‘sentinel’ value would be useful to avoid the NULL marks.

Now, let us assume that you are interested in getting the whole Price data for the Product primarily identified by ProductNumber 1750 on Date 2 June 2017. Seeing that a Price assertion (or row) is current or effective during the entire Interval that runs from (i) its StartDate to (ii) its EndDate, then this DML operation
 SELECT ProductNumber,
        ProductEtcetera,
        PriceAmount,
        StartDate,
        EndDate
   FROM PriceWithEndDate
  WHERE ProductNumber = 1750        -- (1)
    AND StartDate    <= '20170602'  -- (2)
    AND EndDate      >= '20170602'; -- (3)

-- (1), (2) and (3): You can supply parameters in place of fixed values to make the query more versatile.

yields the result set that follows
ProductNumber  ProductEtcetera     PriceAmount  StartDate   EndDate
-------------  ------------------  -----------  ----------  ----------
         1750  Price time series…         1000  2017-06-01  2017-06-03

which addresses said requirement.
As shown, the PriceWithEndDate view plays a paramount role in obtaining most of the derivable data, and can be SELECTed FROM in a fairly ordinary way.
Taking into account that your platform of preference is PostgreSQL, this content from the official documentation site contains information about “materialized” views, which can help to optimize the execution speed by means of physical level mechanisms, if said aspect becomes problematic. Other SQL database management systems (DBMSs) offer physical instruments that are very alike, although different terminology may be applied, e.g., “indexed” views in Microsoft SQL Server.
You can see the discussed DDL and DML code samples in action in this db<>fiddle and in this SQL Fiddle.
Related resources

In this Q & A we discuss a business context that includes the changes of Product Prices but has a more extense scope, so you may find it of interest.

These Stack Overflow posts cover very relevant points regarding the type of a column that holds a currency datum in PostgreSQL.

Responses to comments

This looks similar to work that I did, but I found it much more convenient / efficient to work with a table where a price (in this case) has a startdate column and an enddate column - so you're just looking for rows with targetdate >= startdate and targetdate <= enddate. Of course, if the data isn't stored with those fields (including enddate 31st December 9999, not Null, where no actual end date exists), then you'd have to do work to produce it. I actually made it run every day, with end date = today's date by default. Also, my description requires enddate 1 = startdate 2 minus 1 day. – @Robert Carnegie, on 2017-06-22 20:56:01Z

The method I propose above addresses a business domain of the characteristics previously described, consequently applying your suggestion about declaring the EndDate as a column —which is different from a “field”— of the base table named Price would imply that the logical structure of the database would not be reflecting the conceptual schema correctly, and a conceptual schema must be defined and reflected with precision, including the differentiation of (1) base information from (2) derivable information.
Apart from that, such a course of action would introduce duplication, since the EndDate could then be obtained by virtue of (a) a derivable table and also by virtue of (b) the base table named Price, with the therefore duplicated EndDate column. While that is a possibility, if a practitioner decides to follow said approach, he or she should decidedly warn the database users about the inconveniences and inefficiencies it involves. One of those inconveniences and inefficiencies is, e.g., the urgent need to develop a mechanism that ensures, at all times, that each Price.EndDate value is equal to that of the Price.StartDate column of the immediately successive row for the Price.ProductNumber value at hand.
In contrast, the work to produce the derived data in question as I put forward is, honestly, not special at all, and is required to (i) guarantee the correct correspondence between the logical and conceptual levels of abstraction of the database and (ii) ensure data integrity, both aspects that as noted before are decidedly of great importance.
If the efficiency aspect you are talking about is related to the execution speed of some data manipulation operations, then it must be managed at the appropriate place, i.e., at the physical level, via, e.g., an advantageous indexing strategy, based on (1) the particular query tendencies and (2) the particular physical mechanisms provided by the DBMS of use. Otherwise, sacrificing the appropriate conceptual-logical mapping and compromising the integrity of the data involved easily turns a robust system (i.e., a valuable organizational asset) into a non-reliable resource.
Discontinuous or disjunct time series
On the other hand, there are circumstances where retaining the EndDate of each row in a time series table is not only more conenient and efficient but demanded, although that depends entirely on business-environment-specific requirements of course. One example of that kind of circumstances comes about when

both the StartDate and the EndDate pieces of information are held before (and retained via) every INSERTion, and
there can be Gaps in the middle of the distinct Periods during which the Prices are Current (i.e., the time series is discontinuous or disjunct).

I have represented said scenario in the IDEF1X diagram displayed in Figure 2.

In that case, yes, the hypothetical Price table must be declared in a manner that is similar to this:
CREATE TABLE Price (
    ProductNumber INT  NOT NULL,
    StartDate     DATE NOT NULL,
    EndDate       DATE NOT NULL,
    Amount        INT  NOT NULL,
    --
    CONSTRAINT Price_PK            PRIMARY KEY (ProductNumber, StartDate, EndDate),
    CONSTRAINT Price_to_Product_FK FOREIGN KEY (ProductNumber)
        REFERENCES Product (ProductNumber),
    CONSTRAINT DatesOrder_CK       CHECK       (EndDate >= StartDate)
);

And, yes, that logical DDL design simplifies administration at the physical level, because you can put up an indexing strategy that encompasses the EndDate column (which, as shown, is declared in a base table) in relatively easier configurations.
Then, a SELECT operation like the one below
 SELECT  P.ProductNumber,
         P.Etcetera,
        PR.Amount,
        PR.StartDate,
        PR.EndDate
   FROM Price   PR
   JOIN Product P
  WHERE P.ProductNumber = 1750
    AND StartDate      <= '20170602'
    AND EndDate        >= '20170602';

may be used to derive the whole Price data for the Product primarily identified by ProductNumber 1750 on Date 2 June 2017.


Merge Replication: Service Account Password Change

If the same username and password is to be used on all servers, you will need to make that change on all machines, or rather the replication agent jobs.

If you change the password but miss one of the subscribers, replication will not break. However, it will fail with login errors when the subscribers attempt to connect to the publisher and the password hasn't been updated on the subscriber. As soon as you update the passwords on all subscribers, they will run again without error.

This is a typical scenario for any password change, regardless of if replication is involved or not.

It may be prudent to consider updating your security policy for replication/service account usage and deploy dedicated accounts for this topology instead of using the service account multiple times. This is a security best practice and would allow you to increase the security of your systems.


SQL Server 2008 error when changing the number of cores

Check the version of SQL Server. Enterprise version may be required.


How could i search between two string type date

If you encounter performance problems you could store the dates as integers instead of varchar. 1397-12-09 becomes 13971209, all the numbers remain in sequence so you can still carry out BETWEEN queries. Then convert your results to date format at the final point of presentation.


To normalize or not to normalize? (10 fields)

If the fields are always going to have a value and are always read as a group, then option 1 is normalized.

The column names could use some work, but I assume they have been obfuscated.

There is a third option which is to split the two types out into separate tables.  This would be appropriate if either type grouping was optional and appear separately.

Option 2 is appropriate where the fields are independent of the id (Primary key).  From your description, this is not the case.

Even if you do model the two types as separate tables in the design model, one of the implementations of subtypes is to incorporate them in the main table using nullable columns.


Trying to find a way fit an extremely large index in to memory

Fitting the index into RAM is not a useful goal.  Decreasing the number of blocks of the index to use is a useful goal.

"Using intersection" is not as fast as the following composite index.  Both of your queries would benefit from

INDEX(username, bare_peer, timestamp)

When adding it, you can drop i_username since it is a prefix of this.

PARTITIONing, even if possible, is unlikely to improve performance.

Also, normalizing out the 4 names (in the same table?) would shrink this table significantly, thereby helping performance a little.

What version of MySQL?  If it a new one, adding the above index should be relatively non-invasive.

More on designing indexes:  http://mysql.rjweb.org/doc.php/index_cookbook_mysql


Remove specific duplicates (all but latest)

You could implement a query using row_number() to delete everything but the most recent row. This partitions the data by the employee_id and orders it by the autoId column, then you delete everything that is greater than the first row number:

;with cte as
(
  select [EMPLOYEE_ID], [ATTENDANCE_DATE], [AUTOID],
    row_number() over(partition by [EMPLOYEE_ID], [ATTENDANCE_DATE]
                      order by  [AUTOID] desc) rn
  from dbo.ATTENDANCE
)
delete
from cte
where rn > 1;

See SQL Fiddle with Demo


Database schema size result differs from file size when exported

The export does not contain the indexes. It only contains the definition of the indexes but not the data that is stored in there.

Additionally the size of SQL export will also not reflect the size of your data (just think of the SQL keywords that are needed for the SQL export).

When you move data from one representation to another (MySQL "on-disk" format -> Binary Export -> SQL Script) it is to be expected that the different formats show a different size.

But I don't understand what you problem actually is.


All in all I think your Key Relationships are straightfoward enough and clear enough that I could easily find my way. You'll probably find a few places to change as you mock-up and eventually code your stored procedures and other processes. Here are my thoughts as I looked through it.

Replace parent with something more generic, like guardian. This use of language opens the door for other authority figures for the student, such as grandparents and adopted parents, as well as allow for a flag that indicates the person is an emergency contact or is allowed to pick the child up.
Separate the field [Full_Name] into two fields; [Given_Name] and [Surname]. This helps when sorting by name during programming. (It's better to have the two Williams sisters show up next to each other on a report than all the Mary's of the school grouped together).
Suggest moving the [term] field under the [subject] table into the [class] table. This field can be used to indicate if Bobby Tables was in that class during the Spring of 2010 or the Fall of 2009, with that specific teacher in a specific room. I would have another table that had the date ranges for those terms. (As a side note, INT(11) may be a little overkill for it. A hundred years of one-week terms would only really result in 5000 terms, with would only need INT(4))
On the subject of promoting students to the next year, it could be done with a new field under the [student] table, with a simple INT(2) to indicate the year of their education. A stored procedure that is run at the end of the school year could check their credits earned and move them up.
A consideration can be made also to include in the [subject] and [teacher] tables would be a department, such as Math, Science, or English. This could be used to help group together all the Science teachers, or show all the History classes that are available that term, especially if the school is large enough to have more than one session of a particular class.


How to use reference to another table in aggregate function

The OUTER APPLY is probably not too bad, but if you did want to replace it with something else here is another option:

With cte As (
    Select GroupId,
        StatusOfRequest,
        HasCertificate = iif(Exists (Select 1
                                       From FK.Certificate As c
                                       Where c.CertificateRequestID = cr.ID), 1, 0)
      From FK.CertificateRequest As cr)
Select GroupID,
    OnCreateCount = SUM(CASE WHEN StatusOfRequest = 1 AND HasCertificate = 0 THEN 1 END),
    ...
    NewCount = SUM(CASE WHEN StatusOfRequest = 1 AND HasCertificate = 1 THEN 1 END),
    ...
  From cte
  Group By GroupID;

This assumes you have a version of SQL Server which supports iif and common table expressions. If not, easy to translate to CASE and subquery respectively.


SQL Server 2008 R2 not starting after moving model database

You probably moved the model database without issuing the required ALTER DATABASE statements. In the errorlog you will find the path where SQL Server expects to find the model database files (data and log) and you can move back the files to that location and start the service again.

After the service has started, you can follow the instructions given here to move the model database to a different location.

Another possible cause could be the SQL Server service account is unable to access the path where the model database files are stored. Again, you can check it in the errorlog.


Can I write SELECT query which returns table name after FROM keyword?

If you have a somewhat limited number of tables that could be returned by the query, you could have the query select from all of them, but only return results for the one matching the data returned by the table1/table2 query.  That would look something like this:

With TableVariable As (
   SELECT table_name FROM table2
   WHERE mi_prinx=(
      SELECT mip1 FROM table1 WHERE mip1=184425 AND startmi1=0 GROUP BY mip1,startmi1 HAVING count(*)>=4)
   )
SELECT cap FROM TableA WHERE 'TableA' = (SELECT Table_Name FROM TableVariable)
UNION ALL
SELECT cap FROM TableB WHERE 'TableB' = (SELECT Table_Name FROM TableVariable)
UNION ALL
SELECT cap FROM TableC WHERE 'TableC' = (SELECT Table_Name FROM TableVariable)
UNION ALL
SELECT cap FROM TableD WHERE 'TableD' = (SELECT Table_Name FROM TableVariable)
UNION ALL
SELECT cap FROM TableE WHERE 'TableE' = (SELECT Table_Name FROM TableVariable)
UNION ALL
SELECT cap FROM TableF WHERE 'TableF' = (SELECT Table_Name FROM TableVariable)
;

If the number of tables is too long then you'll need to use pl/sql, which may include the dynamic SQL mustaccio mentioned.


How to select events based an adjacent times?

You may simply do a self-join, like:

SELECT r1.sdate, r1.rno, r1.bno, r1.eid,
       r2.* -- choose the r2 fields that are needed
FROM room r1 JOIN room r2
 ON (r1.rno=r2.rno and r1.bno=r2.bno and r1.sdate=r2.sdate
   and r1.stime + r1.duration = r2.stime);

This answers the query: what are the adjacent events?, by returning event x and event y where y follows x, as a relationship.

The proposed SQL statement in your question answers a question that seems slightly different, like: which events are involved in an adjacency relationship? The structure of the result in this case would be one row per such event, then.

One way of getting that is to build it on top of the above query and extract the r1's and r2's in separate rows, UNIONing the result (including the deduplication that UNION does). For example:

WITH list AS (
  SELECT r1.sdate, r1.rno, ...
         r2.sdate AS r2_sdate, r2.rno AS r2_rno, ...
  FROM room r1 JOIN room r2
   ON (r1.rno=r2.rno and r1.bno=r2.bno and r1.sdate=r2.sdate
     and r1.stime + r1.duration = r2.stime)
)
SELECT sdate, rno, ... FROM list
UNION
SELECT r2_sdate, r2_rno, ... FROM list


Pros and Cons of having additional columns in join table of many to many relationship

In general, the wider the table the slower the query. But slower doesn't necessarily mean slow.

The number of joins probably has more to do with the use of id numbers than anything else. Natural keys can speed performance dramatically when the important information is carried in the key. (This happens more often than you might think.)

The M:N table is the natural and sensible place to store information about that M:N relationship.  So adding columns to store information about that relationship makes perfectly good sense. Wider tables are usually slower, but narrow tables that don't store data are less useful.


Let's look at the way you created the user.

EXAMPLE : I have a database called zipcodes. I will create a user called rolando

mysql> grant all on zipcodes.* to rolando identified by 'blahblah';
Query OK, 0 rows affected (0.04 sec)

mysql> show grants for rolando;
+--------------------------------------------------------------------------------------------------------+
| Grants for rolando@%                                                                                |
+--------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'rolando'@'%' IDENTIFIED BY PASSWORD '*446525BB82B5E22BD9E525261D37C494F623C52B' |
| GRANT ALL PRIVILEGES ON `zipcodes`.* TO 'rolando'@'%'                                                  |
+--------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

I specified rolando without an IP or netblock. By defualt, '%' is used. That should allow remote IP address to login.

You said you tried to login to your account with

 mysql -u violetkiwi -p

Why did this not work? Simply put, you were not connecting remotely. You created the user violetkiwi@'%'. You should create the user violetkiwi@localhost. Then, it can cleanly connect.

You need to make sure your mysql authentication scheme has been cleaned up

MySQL 5.0 Certification Study Guide says on Page 498 Paragraph 6 in its bulletpoints brings out how to cleanup the authentication process:

  On Unix, MySQL comes with a mysql_secure_installation script that can
  perform several helpful security-related operations on your
  installation. The script has the following capabilities:

  Set a password for the root accounts
  Remove any remotely accessible root accounts.
  Remove the anonymous user accounts. This improves security because
  it prevents the possibility of anyone connecting to the MySQL server
  as root from a remote host. The results is that anyone who wants to
  connect as root must first be able to log in on the server host, which
  provides an additional barrier against attack.
  Remove the test database (If you remove the anonymous accounts, you
  might also want to remove the test database to which they have
  access).

A remotely accessible account is an account whose host is % which allows anyone and his grandmother. To find those accounts, run this query:

SELECT user,host FROM mysql.user WHERE host='%';

You should also be cognizant of how MySQL user authentication occurs.

According to MySQL 5.0 Certification Study Guide pages 486,487, the following describes mysql's authentication algorithm:

  There are two stages of client access control:

  In the first stage, a client attempts to connect and the server either
  accepts or rejects the connection. For the attempt to succeed, some
  entry in the user table must match the host from which the client
  connects, the username, and the password.

  In the second stage (which occurs only if a client has already
  connected sucessfully), the server checks every query it receives from
  the client to see whether the client has sufficient privileges to
  execute it.

  The server matches a client against entries in the grant tables based
  on the host from which the client connects and the user the client
  provides. However, it's possible for more than one record to match:

  Host values in grant tables amy be specified as patterns contains
  wildcard values. If a grant table contains entris from
  myhost.example.com, %.example.com, %.com, and %, all of them
  match a client who connects from myhost.example.com.

  Patterns are not allowed for the User values in grant table entries,
  but a username may be given as an empty string to specify an anonymous
  user. The empty string matches any username and thus effectively acts
  as a wildcard.

  When the Host and the User values in more than one user table record
  match a client, the server must decide which one to use. It does this
  by sorting records with the most specific Host and User column values
  first, and choosing the matching record that occurs first in the
  sorted list, Sorting take place as follows:

  In the Host Column, literal values such as localhost, 127.0.0.1,
  and myhost.example.com sort ahead of values such as %.example.com
  that have pattern characters in them. Pattern values are sorted
  according to how specific they are. For example, %.example.com is
  more specific than %.com, which is more specific than %.

  In the User column, non-blank usernames sort ahead of blank usernames.
  That is, non-anonymous users sort ahead of anonymous users.

  The server performs this sorting when it starts. It reads the grant
  tables into memory, sorts them, and uses the  in-memory copies for
  access control.

SUMMARY

If you have any users that must have remote access, change the host of each user that has % into the public IP you will be connecting from.

For example, if the user myuser must connect from 208.21.42.184, replace the host like this

UPDATE mysql.user SET host='208.21.42.184' WHERE user='myuser' AND host='%';
FLUSH PRIVILEGES;

You should also be checking for the presence of any test databases. The presence of test databases. Any database named test or whose first five(5) characters are test_ need to be renamed immediately !!! I wrote an earlier post on how and why.


Statistics: CPU Time vs Elapsed Time. What is more important?

CPU Time is the quantity of processor time taken by the process.  This does not indicate duration.  "Elapsed Time" represents the total duration of the task.  If a given task uses a parallelism of 8 (i.e. 8 threads), and each thread is used at a rate of 100% over the entire duration of the task, CPU time could be 8000ms, while Elapsed Time would only be 1000ms.

Therefore, shorter "Elapsed Time" indicates faster response time.

You may want shorter CPU time and longer elapsed time if you are concerned about CPU pressure, and don't care about user experience.

You may accept longer CPU time if that results in a drop in elapsed time, since that might indicate the user is waiting less, at the expense of higher CPU utilization.

Arguably, the more important metric for query tuning would be wait times.


"Approximately equal" between Sync and Async operations for PageCleaners

The idea is that you want as many of your writes as possible to be asynchronous. On the other hand, pool_data_writes will always be greater than pool_async_data_writes (ditto for index writes).

The tolerance depends entirely on your system. If each minute you have 3 pool_data_writes and only 1 pool_async_data_writes, you obviously have twice as many synchronous writes as you have asynchronous, but it's hardly a reason for concern.

If, on the other hand, each minute you have 300K pool_data_writes and 290K pool_async_data_writes, the difference appears to be minor, but you still have 10000/60 ~ 166 waits per second, which is pretty bad.

Please keep in mind that num_iocleaners is not the only configuration parameter that affects the write efficiency: http://www-01.ibm.com/support/knowledgecenter/SSEPGG_10.5.0/com.ibm.db2.luw.admin.perf.doc/doc/c0011381.html?lang=en

PS. In your comment you compare asynchronous reads with writes, which doesn't make much sense.


How would one create an SSRS datasource to quickbooks?

I believe you need to set up an ADO.Net DataSource in SSRS:

https://www.cdata.com/kb/tech/quickbooks-ado-ssrs-server.rst


It's impossible to tell without investigation. Potentially it could be mistakes when you created the slave, it could be direct writes to the slave, bugs in replication, bugs in an app with SET binlog=0, etc.

Worse, it's very hard to investigate post-factum. You need to prepare for the data drift troubleshooting.

pt-table-checksum would be your first step. It's a mature tool, safe to use on prod, but problems are possible, too. Most likely, locking issues, deadlocks. If need to be extra paranoid, I set --chunk-time to something like 0.1 or even less.

Find differences in the data, review it (you may want to use twindb_table_compare to find which rows are missing/extra/different), and fix the mismatches (with pt-table-sync or rebuild the slave depending how big the data drift is).

And put pt-table-checksum in cron. If one run takes long time (say 1 - 2 days) I recommend to split discovery and alerting parts (i.e. the heavy pt-table-checksum populates percona.checksums and a light script checks percona.checksums and alerts if inconsistencies are found). On my systems I run pt-table-checksum continuously.

Then, when an inconsistency is discovered inspect the data, the binlog and then you'll figure out where it comes from.


What impact do queries have on a server's resources?

A query takes some combination of CPU and I/O.  It might be 100% and 0% if everything it needs is cached in RAM, or it might be 1% and 99% if nothing is cached.  Or anywhere in between.

Only one "core" of the CPU will be used for one connection in MySQL.  Multiple connections could use all the cores; but I rarely see that.

Another issue is "locks" or "mutexes".  This is where one connection is busy doing something, but has a lock on something; and another connection wants that something.  Now the second connection has to wait, not using CPU or I/O.

So, 0.1 seconds (or a full minute) does not really say anything other than that the user had to wait that long for the answer.  It will be some combination of CPU (one core), I/O, and being blocked.

The Operating system is responsible for parceling out resources such that the system never appears to be "locked up", and everything gets done eventually.

I have seen a query take 57 hours; it was mostly I/O.  I have seen queries that I could estimate would take months.  And I have seen queries that take far less than 0.1 second.

And I have seen systems that are running over a 1000 queries per second even though every query took more than 1/1000th second.  This is the magic of Operating systems.


Join on clustered tables shows greater cost than non-clustered tables?

Most likely using a cluster for this query won't be beneficial.

A cluster in Oracle allows data from multiple tables to be stored physically close when they share a common key (here dno I suppose). This allows some query to perform better but a cluster will intrinsically consume more space than standard heap tables because for each key there will be some unused space. Insert-only heap tables on the other hand are one of the most efficient way to store data space-wise, since the rows fill all blocks nicely up to the HWM.

In your case since you don't have a filter so all rows will be read, producing a FULL SCAN of the data. Because the rows are stored in a more compact manner in heap tables, the cost will be less than the cost for the cluster.

The cluster, however, should have an edge when you look for a specific key, but this will also depend on the distribution of the data (number of rows per key), and on the length of the rows. You could build an example where the heap tables with regular B-Tree indexes will outperform a cluster for single-key queries.

In conclusion, clustering tables in Oracle will help for some queries, but will also be hurtful to others, it has restrictions and drawbacks, it is not a silver bullet for optimal performance. Heap tables are the default for a good reason: they have good performance for most queries.


How to trunc double precision number without rounding?

Why not simply do the following?

SELECT trunc(num, 2)
 FROM (VALUES (         9.999999                  ),
              (         9.999999999999999999999999),
              (9999999999.999999999999999999999999)
      ) t(num);

/*
     trunc
───────────────
          9.99
          9.99
 9999999999.99
*/

This way you just truncate it, without assuming anything about the digits.

Notes:

it looks like you have numbers stored as text.  Avoid this, if you can.  If this is not the case, just don't explicitly cast anything.  If it is, then:
It might not be obvious, but my example is using numeric - this is the type the parser interprets 9.99999, for example:

SELECT pg_typeof(9.99999);
 pg_typeof
───────────
 numeric

That is, use an unlimited numeric in your example.  This would accomodate all your digits.

According to the documentation:

  The types decimal and numeric are equivalent.


IntelliSense not working but it is enabled

There are number of possibilities where IntelliSense not working:

There are cases where the local cache used by IntelliSense becomes stale. Refreshing the cache is easy but not necessarily obvious.

There are two ways to refresh the cache:

Hit Ctrl+Shift+R
Go to Edit -> IntelliSense -> Refresh Local Cache

If IntelliSense is still not working, then make sure you have IntelliSense enabled. To check this from the T-SQL Query editor window of the current database:

Go to Tools -> Options -> Text Editor -> Transact-SQL -> General -> IntelliSense
Go to Query -> IntelliSense should be selected
Also verify that the T-SQL Editor does not launch in SQLCMD Mode. To check:

Go to Tools -> Options -> Query Execution -> SQL Server -> General
Make sure "By default, open new queries in SQLCMD mode" is unchecked.
or Go to Query ->  SQLCMD Mode should not be selected

Read More


There is no way to specify the time-span for which audit-files are kept. They are kept based on size and number of rollover files. The old ones will be deleted then. If rollover = 0 and you restart the audit every day, you would only ever have 1 file for example. If you want an "advanced archive mechnanism" you have to store the files elsewhere, i.e. in a special Datawarehouse. (recommended)


Trace ODBC commands: What is the registry path for TraceSQLMode and where is SQLOUT.TXT?

I managed to dig up the path from a comment on this page. On my machine, with Access 2016, the TraceSQLMode registry key is found here:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\ClickToRun\REGISTRY\MACHINE\Software\Microsoft\Office\16.0\Access Connectivity Engine\Engines\ODBC\TraceSQLMode

I found something on this page that mentioned that the sqlout.txt file is stored in the Default database folder as indicated in MS Access Tools-> Options-> General-> Default database folder. In my case, it is located here:
C:\Users\[my user name]\Documents\sqlout.txt


Why one database is so much slower than other?

There could be a bunch of reasons.  Chances are that the data is different in the database and the planner is generating different query plans as a result.  Other differences may include caching differences (maybe the data in one query is in the cache and the other one is not?)....

There are two approaches you can take to troubleshooting here.  The first is to only look at performance when we know the data is cached to the extent possible and the second is to only look at performance when we know the data is not cached.  To do the first, you run each query twice and discard the timing on the first result.  To do the second, we cat a hard drive to /dev/null between each run.   I mention this though because it is very important to compare apples to apples here.  If you find that caching is the whole difference (which it probably is not) then you know the issue is in usage patterns in the two dbs.  So assuming we are looking at cache-active responses, here's my suggestion:

Run each query once by itself, followed by EXPLAIN ANALYZE [query] and post the explain analyze results in your question if you need further help (the first run is just to ensure caches are being used to their fullest).  Note you need to post the query along with the query plan for us to make much sense of it.
A very useful tool for looking at explain output is http://explain.depesz.com/ and I would suggest making use of that too.
In the explain output look for sequential scans returning a large number of rows that should be unnecessary (because only a few rows are searched for) and mismatches between estimates and actual return values.  For seq scans returning many more rows than needed (say more than 10x as much), then chances are the query might need some tuning or an index might solve the problem.

A second point is that a 5 sec query IME is usually a reporting query of some sort (people don't like to wait 5 sec for data entry).  If this is the case, then it may be aggregating a fair bit of data, and the data set size may be much larger on the db which is posing the problem.  More memory can obviously help here but indexes only get you so far before a physical order search is quite a bit faster.  I have a few 5-15 sec data entry queries but they are rare and much more like reporting queries ("show me all unpaid invoices between the following dates" which return thousands of rows for processing) and only 5-15 sec on db's with gigabytes of accounting data.  If 5 sec is sufficient it is usually either reporting or closely related.


I am trying to connect My R studio to SQL Database on the Remote Desktop

Basically, when you failed to connect to your SQL Server, the issue could be:

Network issue. SQL Server configuration issue.
Firewall issue.
Client driver issue.
Application configuration issue.
Authentication
    and logon issue.

Check this link for more details.

In your error message it is just a login failure:

  Login failed for user 'HMINDIA\274074'

you need to create a login.
there are windows logins and sql logins:

for example (windows login - attached to the active directory):

USE [master]
GO

CREATE LOGIN [my_domain\my_login]
FROM WINDOWS WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english]
GO

--grant the access and permissions accordingly
--in this case it is a sysadmin
--ALTER SERVER ROLE [sysadmin] ADD MEMBER [my_domain\my_login]
GO

example of creating a sql login:

USE [master]
GO

CREATE LOGIN [my_ETL_login] WITH PASSWORD=N'Hare_Krishna108',
DEFAULT_DATABASE=[master],
DEFAULT_LANGUAGE=[us_english],
CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO

-- grant access to this login
-- this login will be able to access and read database CAMemeMail:

    USE [CAMemeMail]
    GO
    CREATE USER [my_ETL_login] FOR LOGIN [my_ETL_login]
    GO
    USE [CAMemeMail]
    GO
    ALTER USER [my_ETL_login] WITH DEFAULT_SCHEMA=[dbo]
    GO
    USE [CAMemeMail]
    GO
    ALTER ROLE [db_datareader] ADD MEMBER [my_ETL_login]
    GO

And don't forget the firewall


With 2.5 million items I would expect your indexes to be rather large.  Particularly if you are indexing on large fields.  Also just as an FYI if your primary key is large that doesn't help either.  Any non clustered index has the primary key (assuming that is your clustered index) in it regardless of if you include it or not.  It has to as a reference back to the table.

So if you have a large primary key (and only if you have a large primary key) you might consider creating an identity column and changing that to your clustered primary key.  You can keep your existing primary key as a non-clustered unique index.  Also if you are worried about index fragmentation REBUILD/REORG it.  It's part of the ALTER INDEX command in SQL 2008 and up.  You can even do it on line.

My suggestion to you is to add the indexes you need (within reason).  Generally 3 or 4 non clustered indexes isn't going to hurt performance.  Honestly you can probably get away with 10 or so without a problem if you really need it.  Test on your system as mileage may vary.  Personally I would make sure that you have a clustered index as well.  Also add a maintenance job of some type to do your index REBUILD/REORG.  There are a number of them out there that will even check your current fragmentation and REBUILD or REORG intelligently as needed.


SQL Server Installer appears to be resetting MSDB Recovery Mode to Simple

This is already mentioned in the documentation at Microsoft site as below:

  By default, msdb uses the simple recovery model. If you use the backup
  and restore history tables, we recommend that you use the full
  recovery model for msdb. For more information, see Recovery Models
  (SQL Server). Notice that when SQL Server is installed or upgraded and
  whenever Setup.exe is used to rebuild the system databases, the
  recovery model of msdb is automatically set to simple.

You need to change the recovery model after doing any installation or service pack as per your requirement.

Hope above clarifies your concern.


SQL Server 2005 on Windows 7 along with SQL Server 2008 R2 Express edition

I doubt you can use the SQL Server 2005 client tools for SQL Server 2008 R2. But if your main issue is the inability to use profiler, I suggest just running server-side traces instead. You don't get the fancy GUI, but you can still see all the same data.

Running a trace without profiler: SQL Server Performance Statistics Using a Server Side Trace

Note, one thing you won't be able to do with server-side tracing is to replay a trace.


Here is the killer deal: it January it was costing $12k to buy 864*GB* of RAM. You can get a lot of bang for the buck by simply increasing the RAM of your server up to the point that you will never hit a physical read (after warm up, of course).

Other than that is really hard to give an black or white opinion about either of those data points you present. Sure plan #1 had most physical reads, but are you positive that all tests were done on similarly warmed up cache? Could it be that #1 warmed up the cache for #2, what is your test methodology to ensure all cases are considered on level ground? Even so, if you shell out $500 and double the RAM, would it matter any more? #1 does have the least logical reads...

But then #2 is probably benefit from a high DOP (that one scan can be parallel). Is the wall-clock time of #2 better that #1 after you added sufficient RAM?

How many of these plans run in parallel? Are there tens of queries requesting concurrently a significant memory grant for the hash of #3 and thus creating contention for the RESOURCE_SEMAPHORE? Is the #2 doing a sort and also requesting a memory grant? Will #1 work better since it requires no grant (at least from the info posted...)?

Is really really relative and the question you ask is more like finding one solution for a complex system of equations... there simply could be more that one solutions.

One things is sure: 8M rows should fit in RAM with plenty room to spare. Those physical reads are begging for some memory banks.


How to backup PostgreSQL database (a schema) where a user has access to only one schema

Looks like usage privilege on public schema has been revoked from salesuser. So, you are getting the permission denied error while dumping the data. RESTRICTING PUBLIC SCHEMA

pg_dump, in general will backup all the schemas(sales and public) of your database.
Hence, you need to explicitly mention -n sales or --schema=schema_name while taking the pg_dump and it will work fine.

You can issue command \dn+ in psql to view the schemas and access permissions.


The JSONB object is probably large enough that it is compressed and stored out of line, in a TOAST table.  Having to retrieve and decompress the TOASTed value for every row is going to slow things down by a lot.

I just wouldn't use JSON or JSONB for this purpose if I wanted speed.  If you need to access a small number of keys, pulling them out into the table proper could help.


Return items not found in database in Oracle

You can use this approach as it demonstrated in this SQL fiddle:

SQL Fiddle

Oracle 11g R2 Schema Setup:

create table cust (custname varchar2(20), custid int);
insert into cust values('Andrew', 1);
insert into cust values('John', 2);
insert into cust values('Mary', 3);

Query 1:

with names as (
select 'Andrew' AS search from dual
union all
select 'Mark' AS search from dual
union all
select 'Mary' AS search from dual
)
select n.search, NVL(c.custid, 0) custid
from names n left join cust c
on n.search = c.custname

Results:

| SEARCH | CUSTID |
|--------|--------|
| Andrew |      1 |
|   Mary |      3 |
|   Mark |      0 |

Query 2:

with names as (
select 'Andrew' AS search from dual
union all
select 'Mark' AS search from dual
union all
select 'Mary' AS search from dual
union all
select 'Jo' AS search from dual
)
select n.search, NVL(c.custid, 0) custid,
CASE WHEN c.custid IS NULL THEN 'not found'
 WHEN c.custname = n.search THEN 'exact match'
 ELSE 'partial match' END AS result
from names n left join cust c
on c.custname LIKE n.search || '%'

Results:

| SEARCH | CUSTID |        RESULT |
|--------|--------|---------------|
| Andrew |      1 |   exact match |
|   Mark |      0 |     not found |
|   Mary |      3 |   exact match |
|     Jo |      2 | partial match |

As you can see, you need to put the search values into the cte called names, and then left join it with your table. Using this approach will also allow you to use LIKE operator with a 'result' column, as demonstrated by the second query.

BTW, read this article on how to create a UDF that will convert the IN-list to a table


Cannot set NOCOUNT to OFF inside the trigger execution because the server option "disallow_results_from_triggers" is true

Find the triggers that have SET NOCOUNT OFF and fix them. I can't possibly imagine what good can come from having that line anywhere. NOCOUNT should be ON in every single module on your server.

This will give you the definition (and may introduce some false positives, so check closely), you'll just need to add GO between them and change CREATE to ALTER:

SELECT 'GO
-- ' + QUOTENAME(s.name) + '.' + QUOTENAME(m.name) + '
GO',
  m.definition
  FROM sys.triggers AS t
  INNER JOIN sys.schemas AS s
  ON t.[schema_id] = s.[schema_id]
  INNER JOIN sys.sql_modules AS m
  ON t.[object_id] = m.[object_id]
  WHERE LOWER(m.definition) LIKE N'%set%nocount%off';

You may need to repeat this task in multiple databases.

Once you've fixed all of those, try the job again. If the error continues happening, try to narrow down the trigger(s) responsible and inspect the definition more closely. You may have to reverse engineer the job and all of the things it calls in order to find it, but once you have, post it here.


msdb..sp_send_dbmail does not work with @query only with @body

Have you created a mail profile and assigned an account to the profile?  See the how to by Pinal Dave @ SQL Authority

First create a mail profile, assign the profile a valid account, and enable  Database Mail XP's

Once those steps are done and tested, test your script

    msdb.dbo.sp_send_dbmail
    @profile_name = 'ProfileName'
    --@body='Hi from sql server in a sproc'
    @query='select 1'
    ,@recipients='mkohn@***.com'
   ,@subject = 'Test'


Using ASPState database in the AG cluster with multiple teams using the same ASPState

As a rule it's only one ASPState database per server but we need it to be able to accommodate multiple Web applications.

Your company just dictated your answers and infrastructure, sadly.

  Is it possible to use one ASPState with multiple Web applications? If it is, then how?

Apparently so:

Stack Overflow Answer
Maybe it is or isn't a good idea?

  Should this ASPState database be a part of the AG group?

If you're going to use AGs then it doesn't have to take part in it but it'll need to be loaded and properly setup on all replicas. If you put it in an AG, then you'll only have a single AG for all applications which may or may not be a good thing, depending on the application requirements.

Since the ASP state database is really just about throw away session state data, if there is a failover it won't make a real difference.

  If it's part of the one AG than other AG's won't be able use it.

That's true. If it's not in an AG but loaded on each replica by itself not in an AG that should work as I reference above.


Possibly what happened was that a complex SELECT was run.  It was so complex that it built one or more giant temporary tables that overran the disk tmpdir filesystem.