Does dbms_lob.getlength return the compressed or uncompressed length? If you have tablespace compression turned on, does dbms_lob.getlength return the compressed or uncompressed length of lobs stored on that tablespace? What about LOB compression?

yes , it return them for example at the below:

SET SERVEROUTPUT ON
DECLARE
l_in_blob            BLOB;
l_compressed_blob    BLOB;
l_uncompressed_blob  BLOB;
BEGIN
-- Set some values
l_in_blob     := TO_BLOB(UTL_RAW.CAST_TO_RAW
('This is a long string of words used for this example'));
l_compressed_blob   := TO_BLOB('0');
l_uncompressed_blob := TO_BLOB('0');

-- Compress the string
UTL_COMPRESS.lz_compress
(src => l_in_blob, dst => l_compressed_blob);

-- Uncompress the string
UTL_COMPRESS.lz_uncompress
(src => l_compressed_blob, dst => l_uncompressed_blob);

-- Compare the results with the input
DBMS_OUTPUT.put_line('Input length is    : ' || LENGTH(l_in_blob));
DBMS_OUTPUT.put_line('Compressed length  : ' || LENGTH(l_compressed_blob));
DBMS_OUTPUT.put_line('Uncompressed length: ' || LENGTH(l_uncompressed_blob));

-- Caller responsibility to free up temporary LOBs
-- See Operational Notes in the documentation
DBMS_LOB.FREETEMPORARY(l_in_blob);
DBMS_LOB.FREETEMPORARY(l_compressed_blob);
DBMS_LOB.FREETEMPORARY(l_uncompressed_blob);
END;
/

Input length is    : 52
Compressed length  : 67
Uncompressed length: 52

For more information you can use this site.


Stuck in "query end" status every 5 minutes I'm sick of seeing data in the slow query log, so I decided to check out what the fuss was all about.

An UPDATE query that is running up to tens of times per second is sometimes really slow. Here is the query:

UPDATE `user` SET
    `last_active` = NOW(),
    `last_ip` = '<ip>'
WHERE `name` = '<name>'
LIMIT 1

The table:

CREATE TABLE IF NOT EXISTS `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(16) NOT NULL,
  `last_ip` varchar(50) NOT NULL DEFAULT 'none',
  `last_active` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`),
  KEY `last_active` (`last_active`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=34003;

Usually the query takes ~0.2 ms, but every 5 minutes (accurately every 5 minutes, 10:00:12, 10:05:12, 10:10:12...) it takes over 250 ms, with all time spent with the status "query end".

Any idea what may be causing this? Why would one query run slow every 5 minutes? What is happening every 5 minutes that is affecting "query end"? No other processes are running.

MySQL version 5.5.44

All uses of that table will run faster (some will run a lot faster) if you jettison id and make name the PRIMARY KEY.

Your UPDATE is currently looking in 2 BTrees, plus updating 2 BTrees.  The PK change would change those to 1 and 2.  More importantly it would simply the row locking.

How big is the table?  How much RAM do you have?  What is the value of innodb_buffer_pool_size?  innodb_io_read_threads?  What the heck, let's see SHOW VARIABLES LIKE 'innodb%';

Yes, Please check innodb_buffer_pool_size configuration.

Increase your read io_thread and write_thread,which will help you to figure it out.


Why is a functional dependency described as a "one-to-many" relationship? As far as I know, the definition of a functional dependency reads that, given the attributes X and Y, "each X value is associated with precisely one Y value". But then the author of the article that I read called this a "one-to-many" relationship. Isn't this really "one-to-one"..?

I think that the FD can't be described as "One-to-one" relationships. In One-to-one relationships, the X value identifies a Y value and vice-versa. In FD this doesn't happen. In fact, the Y values can be shared from different tuples with the same X value.

Think to a simple table with only cities, states and nations (And don't care about the redundancy). We know that the state identifies one and one nation, but a single nations doesn't identify a single state.

So, generally  X implies Y but Y doesn't imply X.

A last observation: an one-to-one relationship need that the tables involved in the relationship must have the same number of rows, in order to associate to every a one b and to associate to every b one a. In the textbook you'll find a lot of FD in which this doesn't happen.

So we can describe a FD as an association of a Y value to many (only sometimes one) X values. So it's called an one-to-many relationship.

This example illustrates the concept of functional dependency. The situation modelled is that of college students visiting one or more lectures in each of which they are assigned a teaching assistant (TA). Let's further assume that every student is in some semester and is identified by a unique integer ID.

StudentID   Semester    Lecture             TA
1234        6           Numerical Methods   Azhar
1201        4           Numerical Methods   Peter
1234        6           Visual Computing    Ahmed
1201        4           Numerical Methods   Peter
1201        4           Physics II          Simone

We notice that whenever two rows in this table feature the same StudentID, they also necessarily have the same Semester values. This basic fact can be expressed by a functional dependency:

StudentID → Semester.

Other nontrivial functional dependencies can be identified, for example:

{StudentID, Lecture} → TA
{StudentID, Lecture} → {TA, Semester}

The latter expresses the fact that the set {StudentID, Lecture} is a superkey of the relation.


How to find all invalid document based on jsonSchema validator? One of the new feature in MongoDB 3.6 is jsonSchema validator. I can create a collection with an validator like:
{
  "validator": {
    "$jsonSchema": {
      "properties": {
        "name": {
          "bsonType": "string",
          "description": "name"
        },
        "age": {
          "bsonType": "int",
          "minimum": 10,
          "maximum": 30
        }
      },
      "required": ["name", "age"],
      "bsonType": "object"
    }
  }
}

But what if the collection already existed with some documents, that don't fullfill the validation schema? How can I found out all invalid documents based on the validator?

You can use the $jsonSchema query operator to find relevant documents.
For example, using the mongo shell:
// Retrieve the current validator for the "students" collection
var schema = db.getCollectionInfos({ name: "students" })[0].options.validator

// Find any documents not matching the schema
db.students.find({ $nor: [schema] })

Similarly, you can use the $jsonSchema query operator to confirm expected matches before adding or updating the validator for a collection:
var schema = {
  $jsonSchema: {
    properties: {
      name: {
        bsonType: "string",
        description: "name",
      },
    },
    required: ["name"],
    bsonType: "object",
  },
}

// Find documents matching schema
db.students.find(schema)

As MongoDB documented here To specify JSON Schema validation, use the $jsonSchema operator in your validator expression.

For example, the following example specifies validation rules using JSON schema:

db.createCollection("students", {
   validator: {
      $jsonSchema: {   --- jsonSchema validation
         bsonType: "object",
         required: [ "name", "year", "major", "gpa" ],
         properties: {
            name: {
               bsonType: "string",
               description: "must be a string and is required"
            },
            gender: {
               bsonType: "string",
               description: "must be a string and is not required"
            },
            year: {
               bsonType: "int",
               minimum: 2017,
               maximum: 3017,
               exclusiveMaximum: false,
               description: "must be an integer in [ 2017, 3017 ] and is required"
            },
            major: {
               enum: [ "Math", "English", "Computer Science", "History", null ],
               description: "can only be one of the enum values and is required"
            },
            gpa: {
               bsonType: [ "double" ],
               minimum: 0,
               description: "must be a double and is required"
            }
         }
      }
   }
})

Or As I am able to see in your code , the jsonSchema validation.

"validator" : {
                "$jsonSchema" : {  ---jsonSchema
                    "properties" : {
                        "name" : {
                            "bsonType" : "string",
                            "description" : "name"
                        },
                        "age" : {
                            "bsonType" : "int",
                            "minimum" : 10,
                            "maximum" : 30
                        }
                    },
                    "required" : [
                        "name",
                        "age"
                    ],
                    "bsonType" : "object"
                }
            }

In addition to JSON Schema validation, MongoDB supports validation with query filter expressions using the query operators, with the exception of $near, $nearSphere, $text, and $where.

  but what if the collection already existed with some documents don't
  fullfill the validation creterial?

Validation occurs during updates and inserts. When you add validation to a collection, existing documents do not undergo validation checks until modification.

For Existing Documents

The validationLevel option determines which operations MongoDB applies the validation rules:

If the validationLevel is strict (the default), MongoDB
applies validation rules to all inserts and updates.
If the validationLevel is moderate, MongoDB applies validation rules
to inserts and to updates to existing documents that already fulfill
the validation criteria. With the moderate level, updates to
existing documents that do not fulfill the validation criteria are
not checked for validity.

For example, create a contacts collection with the following documents:

db.contacts.insert([
   { "_id": 1, "name": "Anne", "phone": "+1 555 123 456", "city": "London", "status": "Complete" },
   { "_id": 2, "name": "Ivan", "city": "Vancouver" }
])

Issue the following command to add a validator to the contacts collection:

db.runCommand( {
   collMod: "contacts",
   validator: { $jsonSchema: { --- jsonSchema validation
      bsonType: "object",
      required: [ "phone", "name" ],
      properties: {
         phone: {
            bsonType: "string",
            description: "must be a string and is required"
         },
         name: {
            bsonType: "string",
            description: "must be a string and is required"
         }
      }
   } },
   validationLevel: "moderate"   --- validationLevel moderate , With the moderate level, updates to existing documents that do not fulfill the validation criteria are not checked for validity
} )

The contacts collection now has a validator with the moderate validationLevel:

If you attempted to update the document with _id of 1, MongoDB would apply the validation rules since the existing document matches the criteria.
In contrast, MongoDB will not apply validation rules to updates to the document with _id of 2 as it does not meet the validation rules.

To disable validation entirely, you can set validationLevel to off.

  How can I found out all invalid documents based on the validator?

For Accept or Reject Invalid Documents

The validationAction option determines how MongoDB handles documents that violate the validation rules:

If the validationAction is error (the default), MongoDB
rejects any insert or update that violates the validation criteria.
If the validationAction is warn, MongoDB logs any
violations but allows the insertion or update to proceed.

For example, create a contacts2 collection with the following JSON Schema validator:

db.createCollection( "contacts2", {
   validator: { $jsonSchema: {  ---jsonSchema validation
      bsonType: "object",
      required: [ "phone" ],
      properties: {
         phone: {
            bsonType: "string",
            description: "must be a string and is required"
         },
         email: {
            bsonType : "string",
            pattern : "@mongodb\.com$",
            description: "must be a string and match the regular expression pattern"
         },
         status: {
            enum: [ "Unknown", "Incomplete" ],
            description: "can only be one of the enum values"
         }
      }
   } },
   validationAction: "warn"  --- validationAction is warn, MongoDB logs any violations but allows the insertion or update to proceed.
} )

With the warn validationAction, MongoDB logs any violations but allows the insertion or update to proceed.

For example, the following insert operation violates the validation rule:

db.contacts2.insert( { name: "Amanda", status: "Updated" } )

However, since the validationAction is warn only, MongoDB only logs the validation violation message and allows the operation to proceed:

2017-12-01T12:31:23.738-0500 W STORAGE  [conn1] Document would fail validation collection: example.contacts2 doc: { _id: ObjectId('5a2191ebacbbfc2bdc4dcffc'), name: "Amanda", status: "Updated" }

  Important Note : You cannot specify a validator for collections in the **admin**, local, and config databases.

  You cannot specify a validator for system.* collections.

For your further reference with jsonSchema validation here, here and here


Vertical Elastic Query is not identifying Object I did create an External Datasource, identical to the guide described here https://www.sqlservercentral.com/articles/cross-database-queries-in-azure-sql-database .The process is pretty simple, so just for illustration.
CREATE MASTER KEY ENCRYPTION ...
CREATE DATABASE SCOPED CREDENTIAL ...

CREATE LOGIN <externaldbname> WITH PASSWORD = '<somepassword1>';
CREATE USER externaldbname FOR LOGIN externaldbname;

CREATE USER externaldbname FOR LOGIN externaldbname;

CREATE EXTERNAL DATA SOURCE ...
CREATE EXTERNAL TABLE ..

All executed successfully. Now when I try to select something from the external database, this error is raised

Msg 46823, Level 16, State 1, Line 3
Error retrieving data from sar*****.database.windows.net.carDB.  The underlying error message received was: 'Invalid object name 'SalesLT.Addresstest'.'.

I didn't get the error msg as the method I followed is used/shown in multiple website and tutorials.

It looks permissions/authentication related to me.
Assuming that the 2 databases are on the same logical server, then your checklist of things to validate:
There is a login in the master database named
There is a user in the "carDB" database named  mapped to the login
The SalesLT.Addresstest table exists in carDB
The user  in carDB has SELECT permissions on the SalesLT.Addresstest table
Test the above by connecting to carDB as the user and selecting from SalesLT.addresstest
There is a MASTER KEY created in the database that you are querying from
There is a CREDENTIAL in the database that you are querying from whose identity matches
the LOGIN that was created - same name and password.
The EXTERNAL DATA SOURCE is created in the database you are querying from
The EXTERNAL TABLE is created in the database you are querying from and matches the schema of SalesLT.Addresstest

There doesn't appear to be a SalesLT database or Addresstest table in that walkthrough. Try following it exactly, see if you get the same result.


sudo service mongod start returns error 100 I'm encountering the following error upon executing sudo service mongod start in terminal.

    mongod.service - MongoDB Database Server
   Loaded: loaded (/lib/systemd/system/mongod.service; disabled; vendor preset: enabled)
   Active: failed (Result: exit-code) since Thu 2018-10-18 12:43:22 ACDT; 7s ago
     Docs: https://docs.mongodb.org/manual
  Process: 3155 ExecStart=/usr/bin/mongod --config /etc/mongod.conf (code=exited, status=100)
 Main PID: 3155 (code=exited, status=100)
      CPU: 22ms

Oct 18 12:43:22 david- systemd[1]: Started MongoDB Database Server.
Oct 18 12:43:22 david- mongod[3155]: 2018-10-18T12:43:22.736+1030 I CONTROL  [main] Automatically disabling TLS 1.0, to force-enable TLS 1.0 specify --sslDisabledProtocols 'none'
Oct 18 12:43:22 david- systemd[1]: mongod.service: Main process exited, code=exited, status=100/n/a
Oct 18 12:43:22 david- systemd[1]: mongod.service: Unit entered failed state.
Oct 18 12:43:22 david- systemd[1]: mongod.service: Failed with result 'exit-code'.

I am unfamiliar with this error, could anyone please suggest what the fix might be?

Fixed using this answer from another post:

  Open /etc/mongod.conf and look for dbPath: /xyz/xyz

  Use the following command to provide the rights to mongodb

  sudo chown -R mongodb:mongodb /xyz/xyz

Check the log file on dbath . This is located normally on /var/log/mongodb
The log file is mongodb.log
The problem for me was low space on disk... I had the same issue you have .
I had no empty space on disk so I have used the smallfiles options on the file /etc/mongodb.conf
Simply add the line on this configuration file: smallfiles = true


scenario to update or insert big amount of data I try to find the best scenario to update or insert 1 million contacts in my postgres database.

My actual scenario is:

read contacts 1000 per 1000 and try to insert multiple rows with one request
if my request fails, I go line per line to insert or update my 1000 contacts

It takes 7 minutes for 50K lines.

What is the best scenario to perform my update or insert?

I would do it like this:

Load all data into an empty staging table and use SQL to identify and correct problematic records before moving the data into your database.


Generate the statistics of a table after truncating it and then repopulate it? Is it necessary to generate the statistics of a table after truncating it and then repopulate it?
I have a table with several million rows in Oracle.Every week new data is recharged, truncating the table and then loading the data.

Each time a new data load is made, I must generate the statistics again. Should I also rebuild the indexes?
Some columns are encrypted with TDE.
Thank you

As a general guidelines, it is usually recommended to do a gather stats on table whenever more than 10% of the rows are modified. So in your case, you will need to do a stats gather post data load.
The index stats gather are taken care of if we do table stats gather using cascade option.


Single table with more columns vs multiple tables with fewer columns What would be a better database design for a social network website? A single table with more columns and fewer rows, or multiple tables with fewer columns but more rows?
For example: A user can post an update on their wall or in a group.
Two database designs I can think of are:
Design 1
UserPosts

id
userId
post
datetime

UserGroupPost:

id
groupId
userId
post
datetime

Potential problem : Might require joins, which can (in future) be a slow query.
Design 2
Posts:

id
userId
groupId
post
datetime (where groupid would be null if user posts on their wall)

Potential Problem : Looping over large dataset could take a (long)time.

How can I get better performance when data increases? Is there any other (better) way?

My inclination here would always be design option 1, or at least along those lines. Do not worry too much about trying to eliminate the need to join tables in future queries - any normalized database is going to use joins in any useful queries, that's just relational databases.

Also, why would you necessarily have to join the userPosts and userGroupPosts tables for your website? Would they not be displayed separately? The only reason you would join these tables is maybe when searching for posts, but it shouldn't be too hard to write efficient queries for that. Apart from that you may end up wanting to query the tables for analysis purposes, but that is not the primary purpose of this database.

Design 2 could at the least mean that you end up with a very busy table.

The best option though would be to prototype each and run some tests. Build a prototype of each design option, and do some performance benchmarking on different operations with some dummy data.

For me, as per your current structure Design 2 is better.
You can implement partitioning, optimized query & structured way to create database/table will decrease the execution time. But some case normalization works better, but totally depends on your database designing architecture.


SQL SERVER 2014 Standard - High Availability Listener solution The company has SQL Server 2014 Standard licenses and we're standing the servers up in a VMware environment.

My initial plan is to create 2 instances on each DB server and run Instance1 on SQL1 as primary, Instance2 on SQL2 as primary with failover vice versa.

Questions:

Without the listener available only through Enterprise addition, has anyone used a load balancer to repoint the DB connection when you failover for maintenance?
Is there a better method for failing over with Standard Edition?

The only other solution that comes to mind is DNS CNames/ALIAS and repointing to a different IP address, but that seems inherently flawed.

With SQL Server 2014 Standard Edition you can create a two-node failover infrastructure. For that, you need to have in each node at least Windows Server 2008 R2 SP1 Standard, because you need to first generate a failover cluster with Windows Server.

If you want a multi-instance failover cluster, then you need to generate two SQL Server 2014 installations, since the product works in an Active-Passive mode. I mean, you will need to install the first instance, SQL1 and then you will proceed with the second instance, SQL2. You will need to configure each instance so SQL1 runs in node 1 and SQL2 runs in node 2.

Also, it's very important and recommended, that you have at least a SAN or some device that can provide you with the shared storage schema, since previous to the SQL Server failover cluster installation you need to validate the Windows Server failover cluster support to the SQL Server one.

Why don't you use Database Mirroring instead and configure your applications to connect using a fail-over partner in the connection string (read more about that here Connect Clients to a Database Mirroring Session (SQL Server)). If you require automatic DBM fail-over then you will also need a Witness server (and run in-sync) in order to use High-Safety Mode with Automatic Fail-over.

If you don't want to use failover partner's in your connection strings, rather than using DNS alias redirection, you can also consider using SQL Client Aliases. If you want them to be "Enterprise deployed" you can use group policy to push those registry entries across your Application servers. Remember these solutions are manual (as opposed to the FP being automatic).

Please be aware that Database Mirroring was deprecated in SQL Server 2012 so you should be mindful that if will be removed in a future version:

  This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. Use AlwaysOn Availability Groups instead.

Therefore once you go to SQL Server 2016 SE you can consider upgrading your DBM to a Basic AG. When you are ready to move to Basic Availability Groups please review Overview of AlwaysOn Basic Availability Groups (SQL Server) to understand the limitations between that and Enterprise edition AGs.


why is io_stall_writes_ms so much higher for tempdb? We have the user and system data files on the same disk drive.  The ( io_stall_write_ms / ( 1.0 + num_of_writes ) ) is below 2 for the user files but the tempdb files are typically over 400.  I see that on a few servers and I'm curious if there is a reason it takes longer to write to tempdb than a regular database data file.

SELECT DISTINCT UPPER(LEFT(mf.physical_name, 1)) AS Directory,
( io_stall_write_ms / ( 1.0 + num_of_writes ) ) as result,
io_stall_write_ms, num_of_writes,
fs.database_id,
fs.[file_id]
FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS fs
INNER JOIN sys.master_files AS mf ON fs.database_id = mf.database_id
AND fs.[file_id] = mf.[file_id]

Thank you,

Short Answer: Seeing higher IO stalls may or may not be a problem in an of itself. You need to look at more information to suss out if it you have an issue. It does seem a bit high, yes, but are you suffering? If so, it is probably because either your IO system is not handling the load right (because it can't, because you have everything on one drive or some other reason) or you are doing too much in TempDB (changing the first problem - the IO performance - is probably an easier and more efficient fix, but first determine if you have a problem)

The longer discussion/answer:

There are two questions at play here -

1.) What do I do when I see high IO Stalls?

First off, "high" is in the eye of the beholder. If you were to ask 10 DBAs what "too high" is for IO stalls you'd probably get 2-3 different answers with numbers in them, 5-6 "It depends" answers and one blank stare. My assumption is an average of 400ms is potentially too high here, especially when the other DBs are 2ms or lower for the average stall time.

Regardless of which database is seeing the high stalls you should approach it the same way. An IO stall is what it sounds like... An IO request taking longer than expected.. Stalling. These happen. They happen all the time in a system with resources being shared and finite resources (really all of our systems). They become an issue when the stalls become performance problems or lead to them. So I trust that you are looking here as a proactive part of monitoring or because you were experiencing performance issues that you are troubleshooting. We also don't want to get lost in just IO stalls. We are looking at a piece of the puzzle and not the big picture. It can be troublesome to just look at wait stats or file stats since SQL was last restarted because you are looking at all time and some maintenance window or heavy load window could skew counters. So make sure you look at the full picture.

But when I suspect I have a disk performance issue or see something off in a query like this, I normally follow a process that looks like:

Look at the wait statistics on the server. @swasheck shared a great link as a comment in a below answer. This takes you to Paul Randal's post on looking at and analyzing wait statistics in SQL Server. Go there.  What kind of waits are you seeing? Do you see waits related to IO performance (PAGEIOLATCH_*, IO_COMPLETION, WRITELOG, etc. ?). If you do this is another indication that you have some IO related performance issues, just like the IO stalls. But it gives you another form of agreement here.
Look at the IO performance. In particular, look inside of perfmon at the Physical Disk:Avg Disk Sec/Read and Avg Sec Disk Sec/Write counters. These measure your latency. Watch these counters over a period of time saved to a performance log file. What did you see for averages? If you are seeing numbers over 0.020 seconds (20ms) this could be an issue. If you see numbers over 40-50ms avg or higher is a more firm indication of a problem. Also look at your spikes? How high do they go and how long do they last? If you see spikes into the hundreds of ms and they last for tens or scores of seconds or more and/or happen frequently you are more likely to have an issue with your IO performance for your workload.
Look at your IO setup. What is it? Local disks? SAN? Storage Array? What kind of throughout and IOPs should you see out of this? Is it sufficient for what you are trying to do? You may have undersized your IO for your workload. Don't just look at your physical spindles, RAID settings, etc. Look at your paths to your disks. Are you pushing everything through a single 1GB link that you are sharing with a lot of other traffic? Can you look at disk performance metrics from the storage's perspective.

(Note: for this wait stats analysis and perfmon analysis - look at various periods and type of usage. Do you have different usage statistics at night than you do during the day? Batch processing windows? Maintenance windows where you rebuild a lot of indexes? Look at these tools during each of these periods and understand what you are seeing for each)

Another IO performance consideration here -

You said system DBs and User DBs are shared. Is this production? If so, that isn't always the best scenario. Are you also sharing log file and data files on the same drives? That isn't the best scenario either. What else shares this storage? In a world where you are worrying about spindles and raid groups and disks and have to make decisions on who gets the best performing disks, I tend to (as a general rule of thumb.. which aren't great to have in the DB world but this one tends to hold true) go with my fastest and most dedicated to TempDB (more on that below), then the log files, then the data files. In a world where you have a big pile of disks on a device like a NetApp, Dell Equal Logic or EMC VNX, etc. you don't need to necessarily worry about separation as much - but you still need to make sure that the total throughput and IOPs your system can deliver (and the switches, cards and cables between you and your disks can deliver) is sufficient for your workload.

2.) What are some reasons TempDB could be higher?

So TempDB is a database and it can have IO stalls like any other database as I just discussed. But what are some reasons TempDB can have higher reads? (not exhaustive, I welcome additions or thoughts in edits, other answers or comments) -

Because of your code - Are you using TempDB a lot in your code purposefully? A lot of temp tables and table variables created and destroyed? Doing a lot of things in TempDB like this? That isn't bad or good necessarily, but you might look at that and understand your intentional TempDB usage pattern.
TempDB is a shared workhorse - TempDB is one database that is used as a temporary space for user defined temporary objects and various work tables and operations used by your entire SQL instance. How many user DBs are there? What kind of workload do you see in general? TempDB is one resource for all things to share.
Inefficient queries and insufficient memory - Perhaps there are queries that aren't using indexes tightly enough or are doing large scan and sort operations. Large hash operations, and the memory on the server isn't sufficient for these. These operations will "spill" to TempDB as worktables behind the scenes. Sometimes this can be avoided with looking at your query plans and indexing or query tuning. Sometimes it happens (more so on warehouse workloads, I find). If you have enough memory, this can help, but these queries can still spill at times. Look a this as well.
Are you using Read Committed Snapshot Isolation level with a fair number of updates in your system? This can also result in increased TempDB activity.

The point is - TempDB is used in a lot of ways, and it doesn't surprise me at all to see it as one of your busiest, if not the busiest, database. It also doesn't surprise me when I see it as having the highest number of and highest average stalls of all databases at a client's site. It is the nature of its workload sometimes. Looking at some of the things I've mentioned here can certainly help you determine if these numbers indicate a problem and if so, how to go deeper in solving it.

TempDB is shared amongst all the databases on the instance. So there can sometimes be contention within TempDB for certain pages: SGAM, GAM, and PFS. In a nutshell, these pages keep track of what's been used in TempDB so far, and where space is available for new use.

Typically, this is dealt with by adding multiple data files to TempDB. There are a few different philosophies as to the correct number, but all agree you should have more than one.

Here are a few queries to run...

This one will show you how many files TempDB has and where they are located.

-- tempdb layout
use tempdb
go
exec sp_helpfile
go

This one will show you how many CPUs and cores you have.

-- cores and hyperthreading
select cpu_count, hyperthread_ratio
from sys.dm_os_sys_info
go

This one will show you how many NUMA nodes and cores per NUMA node you have.

-- numa nodes and schedulers
select node_id, online_scheduler_count
from sys.dm_os_nodes
order by node_id
go

This one will show you which pages are experiencing waits in TempDB.

-- see if anything is waiting on tempdb
select *
from sys.dm_os_waiting_tasks
where resource_description like '2:%'
go

Here is an article which goes into a bit more depth on the page contention issue.

OK, so now the philosophy part...  :-)

For myself, if I am on an SMP system, I only want as many files as half the total cores.

If I am on a NUMA system, then I only want as many files as cores per NUMA node.

However, I rarely see any improvement for having more that four files for TempDB. So I usually start with four and monitor contention as explained in the article I linked to.

If I continue to see problems, then I would add two more. Check again, add more, and repeat until the contention disappears.


Advice with date column SQL Server 2005 I have two datetime columns in SQL Server 2005 that I need to query without the time portion of the datetime. Currently, my query looks something resembling this (just an example):

WITH Dates AS (
        SELECT [Date] = @StartDate
        UNION ALL SELECT [Date] = DATEADD(DAY, 1, [Date])
        FROM Dates WHERE [Date] < @EndDate
)
SELECT DISTINCT ID
FROM table t
CROSS APPLY DATES d
WHERE d.[Date] BETWEEN CONVERT(DATETIME, CONVERT(VARCHAR, t.StartDate, 103)) AND CONVERT(DATETIME, CONVERT(VARCHAR, t.EndDate, 103))

Now this results in a full Clustered Index Scan (surprise, surprise). I am trying to think of ways of making this faster (the actual query takes 3 mins :O). I have thought of doing the following but haven't tried any yet as I ran out of time earlier:

Use a computed column with just the date part
Index said computed column (Not sure if this is even possible?)
Use an Indexed view (Again, not sure if this is possible, will work?)

Easiest way would be to update the column and remove all time information, but I cannot do this :(

Any ideas?

Update

Thanks for all of the answers so far. I think the point of the question was missed because I was slightly unclear on what I wanted. My bad. I was only aiming to optimise the date conversion part of the query as the amount of data i'm dealing with is tiny in reality ( < 500,000 after the cross apply with a year long date range). Sorry for the confusion on this.

For those optimising the rest of the query for me, I can see what people are saying by using < > but consider the following:

The parameters that are passed in is a date range (eg. 1st to the end of this month)
The start date in the table can appear before or during the parameters date range (eg. only the end date is in the date range)
The end date in the table can appear during or after the parameters date range (eg. only the start date is in the date range)
Lastly, the start and end date in the table is in the parameters date range.

Personally, given the above, I could never get a < > solution to work. The only way I could get it to work properly and not miss anything is by using a CTE and saying where d.[Date] BETWEEN t.StartDate AND t.EndDate.

I hope this clears things up. Thanks again.

You can do steps 1 and 2: but use the DATEADD/DATEDIFF technique as per this: How to the get current date without the time part

You will most likely be unable to index the computed column because it won't be deterministic with the varchar method

I feel your pain. I wrote the following for that kind of task, I use it regularly

DECLARE @StartDate DATETIME;
DECLARE @EndDate DATETIME; -- these should be typed, no hour

SET @StartDate = '2011-01-01';
SET @EndDate = '2011-01-31';

WITH TALLY AS -- GENERATE AN ON THE FLY TALLY TABLE WITH REQUIRED AMOUNT OF ROWS
(
    SELECT TOP (DATEDIFF(DD,@StartDate,@EndDate)+1) ROW_NUMBER() OVER (ORDER BY (SELECT 1))-1 N FROM sys.objects A
),   DATES AS -- GENERATE ALL D, D+1 POSSIBILITIES, YOU CAN ADJUST TO ADD 23:59:59.999 INSTEAD OF A WHOLE DAY
(
    SELECT DATEADD(DD,N,@StartDate) StartDate,DATEADD(DD,N+1,@EndDate) EndDate FROM TALLY
)
SELECT DISTINCT ID
FROM table t
INNER JOIN DATES d ON t.YourDateWithHours BETWEEN d.StartDate AND d.EndDate -- THIS IS SARGABLE AND DETERMINISTIC, INDEXES WILL BE USED


#1170 - BLOB/TEXT column 'variable' used in key specification without a key length I am trying to create a database table for a blog website, but I have an error from a text variable.
the database structure looks like this:
CREATE TABLE place(
    id INT(8) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(30) NOT NULL UNIQUE,
    author VARCHAR(30) NOT NULL,
    city VARCHAR(30) NOT NULL,
    state VARCHAR(30) NOT NULL UNIQUE,
    latitude FLOAT NOT NULL UNIQUE,
    longitude FLOAT NOT NULL UNIQUE,
    description TEXT NOT NULL UNIQUE,
    post_date DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
)ENGINE = MyISAM;

but, every time I do try to generate the table it shows up this error:
#1170 - BLOB/TEXT column 'description' used in key specification without a key length

The column is OK, but the UNIQUE constraint requires an index that is too big.
Some other things are wrong:  Surely there can be more than one place in a state?  Yet the UNIQUE constraint says that there can be only one row for each state.
Remove UNIQUE from any column definition that is not truly unique.
Also, I strongly recommend you use InnoDB, not MyISAM.
PRIMARY KEY is a Unique Index.
UNIQUE is an index and a uniqueness constraint.
INDEX is a way to access data faster.  But until you have the SELECTs, you may not know which columns (or combination of columns) should be indexed.

CREATE TABLE place(
    id INT(8) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(30) NOT NULL,
    author VARCHAR(30) NOT NULL,
    city VARCHAR(30) NOT NULL,
    state VARCHAR(30) NOT NULL,
    latitude FLOAT NOT NULL,
    longitude FLOAT NOT NULL,
    description VARCHAR(200) NOT NULL,
    post_date DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 UNIQUE KEY place_details (title ,state, latitude, longitude, description)
)ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;


Creating Temporal table without adding DateTime columns I'm an SQL server DBA and new to Temporal tables. We have the following issue:
As part of implementing temporal tables mechanism , there's a need to add to datetime columns (so DWH will have the ability to investigate only last modified columns).

1st problem is that our R&D are trying to avoid from altering table structure (adding columns) as it is a sensitive and big table.

2nd problem is that the original table contains MODIF DATE column.
This column is being updated at the datetime value of the update transaction beginning time warriors I need the COMMIT time.(gaps are critical)

My question:
Is there a possibility to turn the original table to Temporal one , without adding columns and only by using the CREATION date and MODIF date built-in columns?
Will appreciate any support here!

If you're using System-Versioned Temporal Tables then I believe you need not change your table schema. Rather separate tables are created to automatically manage history on your existing tables.
There's also alternatives in SQL Server that shouldn't require you to change your existing table schema either such as Change Tracking or Change Data Capture (CDC).


Location of the mdf file of the database I have a database Project. My problem is where can I find the .mdf and _log.ldf of my database so that I can transfer my database to another user. I am using Microsoft SQL Server 2008 Management Studio

My Ssms.exe is stored in this path

C:\Program Files\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\Ssms.exe

There are few ways to determine the location of the SQL Server mdf file(s) and associated log file(s).

Open Enterprise Manager, right click on the database you are interested in and select properties. Select the Files section and scroll across to the Path and FileName columns.
Open a query window and run the relevant query below and view the Physical_Name column.

SQL 2005 or later:

SELECT * FROM sys.database_files

SQL 2000:

SELECT * FROM dbo.sysfiles

Open a query window and run sp_helpfile and view the FileName column.

Of course, as these files are in use by SQL Server you should not attempt to copy the files to a different location.

The best method is to perform a backup from within Enterprise Manager by right clicking on the database you are interested in and selecting Tasks -> Backup.

Alternatively you can detach your database, copy the files and then attach.

A third alternative once you have a copy of the database running elsewhere, is to set up log shipping or replication.

The path for .mdf and .ldf will be

Root drive(where Os is installed )->

Programfiles(for 64bit applications) or
programfiles(x86) for 32 but applications->

Microsoft SQL Server->

MSSQL11.SQL2012->

MSSQL->

DATA

MSSQL11.SQL2012
This is for users having using SQL 2012...

So you may easily recognize what's the folder name for you as you are using SQL 2008 under "Microsoft SQL Serve" folder in the mentioned path


Does SQL Server redistribute data after adding an additional data file? Imagine a database about 30GB in size using 3 data files on the same disk.

Someone told me to have one data file per processor implemented to get full performance on all cpus. So if I now add three more data files, how's the data redistributed to the new files? Do I have to do something?

The way that datafiles work, any new data will, mostly, get put in the new data file you added. Or in your case the two new data files.

However, SQL Server will not try to re balance your data. This would't make sense either as you will be causing a lot of IO / load on a database for, as far as SQL server knows, no reason.
You can however rebalance your data by recreating your indexes on a new filegroup while dropping the old indexes. The advantage being that you're allocating space in small chunks, getting unfragmented indexes and generally have more control.

You will however have to do this largely manually. And you'll probably want to create full backups before and after each operation.

Every SQL database has an .mdf file where it stores some basic info about itself and data that is added to tables in the database. It cannot be deleted or replaced.

Optimally for every CPU that a server has there should be a datafile (up to eight), normally these are created by adding .ndf files at the time the database is created.

Trying to keep it simple… we can think of an .mdf or .ndf as physical record (maybe an old school vinyl record that can be rewritten, but runs backwards in to out?) if you only have the .mdf as new data gets added, it gets added farther and farther out.  Occasionally something old is deleted and something new is added in its place but mostly new data is added to the outside.  In this vision a CPU is a record player.  It does not matter how many CPU (record players) you have you can only do things to one record at a time.

So if you have say 4 CPU you want to have 4 data files.   These would be one .mdf and three .ndf.  In theory you can use all 4 CPU to read all 4 at once, work gets done 4 times faster (this is theory, reality varies)

Maybe we have an old system with just the one .mdf and no .ndf.  If we add 3 more .ndf new stuff will start to get written there, but the majority will just stay on the old mdf. There is a bunch of theory that says index rebuilds will move stuff around and it will self-balance over time.  In reality I have rarely seen this happen.  So we have to force it to balance, the best (but not only) way, follows.

For this example assume 4CPU and 100GB of data.
Create a new single .ndf named ‘MyDB_Temp.ndf’ or similar, it should be the same size as the current .mdf (100GB)
Use SHRINKFILE, EMPTYFILE to push everything (except ~5MB of “self” info) to ‘MyDB_Temp.ndf’
Use SHRINKFILE, 25GB  (specific size) to shrink the original .mdf to 25% of the desired total size
Create 3 new .ndf files each at the desired size (25GB)
Use SHRINKFILE, EMPTYFILE to push everything off of ‘MyDB_Temp.ndf’, it will auto balance across all four files, with a mix of old and new data on each.
Delete ‘MyDB_Temp.ndf’
*Done, new data gets written across all 4.

Concerns:

If the system is not having performance issues, don’t worry about, just leave it with the single .mdf
This is going to be a resource hog, do not do during production time
Everything gets logged, if simple recover us COMMIT occasionally.  If full recovery run t-log backups
If you have physical limitations on essentially doubling your database size, you can do it in chunks.  If you have 150GB of space, start with ‘MyDB_Temp’ at 40GB and when it is mostly full, stop and trim 30GB off of the .mdf then add it to .ndf
There are lots of variables, and no one size fits most solution.
Sometimes you can balance small databases in a few minutes.  On one occasion, I literally spent months (like 6) moving a little data each night.


How to Recover an InnoDB table whose files were moved around So I have a test db server that was setup on a replication stream.  Over the name an optimize came through that quickly filled up the space on the slaves datadir.  Mysql dutifully was just waiting for some more space.

This datadir is a file system used ONLY as mysql's datadir so there wasn't anything else to free up.

I had a 4 gig innodb test table that wasn't part of the replication stream so I figured I'd try something to see if it'd work, and being a test environment I wasn't too worried if things went horribly wrong.

Here's the steps I took

Flushed the table I was about to move
Placed a read lock on it (even though nothing was writing to it and it wasn't in the replication stream)
Copied the .frm and .ibd over to a filesystem w/ some spare room
Unlocked the table
Truncated that table - this freed up enough space for the optimize to finish have replication start chugging along again.
Stop slaving/shutdown mysql
Copy the file out of tmp back to the data dir
Restart mysql

Nothing shows up in the .err log, things look good. I connect and use mydb; and see the table I was messing with in show tables.  But, if I try

select * from testtable limit 10;

I get the error

ERROR 1146 (42S02): Table 'mydb.testtable' doesn't exist

From what I can tell so far I can read from all the other tables just fine and replication started back up w/o any complaints.

Is there anything I can do to recover from this point?  I can rebuild it from scratch if need be but was curious what others thought about this venture in general.  Was there anything about the series of steps I took that would have ended up w/ more flawless results?

What if this wasn't a test server I couldn't just 'do it live' and see what happens?  What would have the best way to free up space temporarily on a production slave if I had to like that?

The biggest thing most people forget about TRUNCATE TABLE is that TRUNCATE TABLE is DDL and not DML. In InnoDB, the metadata within ibdata1 contains a numbered list of InnoDB tables. Using TRUNCATE TABLE causes the internal metadata id of the InnoDB table to shift. This happens because TRUNCATE TABLE effectively does the following:

Example: To truncate an InnoDB Table called mydb.mytb

USE mydb
CREATE TABLE newtb LIKE mytb;
ALTER TABLE mytb RENAME oldtb;
ALTER TABLE newtb RENAME mytb;
DROP TABLE oldtb;

The new mytb would thus have a different internal metadata id.

When you copied the .ibd file to some other place, the .ibd contains within it the original internal metadata id. Simply putting the .ibd file back does not cause a reconciliation of the internal metadata id with that of the one in ibdata1.

What you should have done is this:

Copy the .ibd file of the InnoDB table. Then, run this

ALTER TABLE tablename DISCARD TABLESPACE;

To bring it back later on, copy the .ibd file back into datadir and then run

ALTER TABLE tablename IMPORT TABLESPACE;

This would have preserved the internal metadata id.

Make sure .frm is always present.

I once helped a client restore 30 InnoDB tables he hosed in the same manner. I had to use another DB server and play some games with adding and dropping InnoDB tables to hunt down the correct internal metadata id.

The client found this article : http://www.chriscalender.com/?tag=innodb-error-tablespace-id-in-file . We used it and it helped a great deal. I hope it helps you.

I experienced with my Mac, before selling to a friend simply copy the XAMPP folder only to my hard drive.
(NOT SUCCESS) Unfortunately, it was bringing trouble to me, because I tried the following steps:
- I install fresh XAMPP and copy the whole \httdocs and var\mysql to my new Mac, those db only with .frm and .ibd, NOT WORKING, I can't still access the tables inside PHPMyAdmin ...
- I tried to install the same xampp version and repeat the above steps, still NOT WORKING.
- Decided to go to bed.

(SUCCESS)
- This morning, I brought my backup disk and try with Windows 7.
- Install fresh latest XAMPP for Windows, c:\xampp
- I have one of website (folder) from my backup \httdocs and correspond database folder inside \var\mysql are READY in my windows 7, I just want to try with one website then try the rest because I have many projects inside httdocs\ and \var\mysql
- I copy the mention httdocs\ folder to windows c:\xampp\httdocs and copy the \var\mysql to c:\xampp\mysql\data

NOT LAST YET
I copy the ib_logfile0, ib_logfile1, ibdata1 from my backup file to windows xampp c:\xampp\mysql\data

I refresh the http://localhost/mywebsite

WOW WOW DONE ... it's working...


sp_send_dbmail fails when mailing queries, but no error returned, query just hangs I have an issue SQL Server 2005.

sp_send_dbmail fails when mailing out with the result of a query (inline or attatchment).

No error message is given / reported, the query just keeps executing but nothing returned (have left it for 28 minutes so far) with no time out etc.

Mails go out fine when no queries are involved.

Does anyone know how to solve this?

Is it possible that you're running on a deadlock situation? Create a test table that you know for sure no one is accessing it, populate it with some sample data, and run a query in proc against that table just to test it out.

I've come across recently myself. The problem, in my case, seemed to be with the number of records returned by the query.

To troubleshoot, try limiting the results by using SELECT TOP


Database design for attendance feature My app has a requirement wherin we need to capture attendance details of a class. A class typically has 50-60 students and there will be records with attendance/absense status of each student for a day.

My idea is having a table like this:

| student_id | class | section | date       | status      |
------------------------------------------------------------
| 2          | 7     |     A   | 2013-10-24 | 1 or X      |

X - Absent , 1 - Present

This would have records for all months for all students across all classes. So 1000 students, 30 record per student per month, this table must be ending with 1000*30*12 = 360,000 records. We'll be pulling attendance report student-wise and class-wise.

My friend has a suggestion to create a table for each year which would look like this:

| student id  | class   | section | month  | date1 | date 2 | date 3 |......| date30 |
--------------------------------------------------------------------------------------
| 1           | 2       |     C   | Jan    | X or 1|  X or 1| X or 1 |      |X or 1  |

This would mean that I'll have the attendance details of each student for a month in a single record. There would be 1000 (student) * 12 (months) = 12000 records only for an year

Please suggest which would be more suitable here, and if there's any other better way of doing it.

Thanks.

I would suggest something different than you have here- a separate table to record the student_class relation:

| student_id | class | date       |
-----------------------------------
| 2          | 7     | 2013-10-24 |

This would have one record for a student where a class was attended. For each class and date for which there is no record, it can be seen that the student did not attend.

If you want to further normalize the design, I would suggest instead three new tables:

Student_Class: (to determine enrolled students)

| class | student_id     |
--------------------------
| 1     | 2              |

Class_Date: (class_date_id surrogate key for pk(class, date)) (to determine dates classes  were held)

| class_date_id | class | date       |
-----------------------------------
| 1             | 7     | 2013-10-24 |

Student_Class_Date: (to determine which students attended which class period)

| class_date_id | student_id     |
----------------------------------
| 1             | 2              |

This would allow you to easily define which classes were held on which date, easy updating etc.

Edit: Added additional detail with student_class implied relation from question context

It is very bad Idea that you suggest, I suggest something other,
There are 50 students in class create 50 attendance tables one for each(Table name should be registration no of students), Table will have 2 attributes one for date and other for status, Because we have to go through single student attendance most of time.
Hope it will make sense.


MySQL: NOT EXISTS vs LEFT OUTER JOIN...IS NULL Is NOT EXISTS or LEFT OUTER JOIN...IS NULL more efficient for finding records in one table that aren't in another? Specifically, I'm trying to figure out which of the two queries below is better:

SELECT table1.id FROM table1 WHERE table1.a='FOO' AND
NOT EXISTS (SELECT 1 FROM table2 WHERE table1.id = table2.id);

or

SELECT table1.id FROM table1 LEFT OUTER JOIN table2
ON table1.id = table2.id WHERE table1.a='FOO' AND table2.id IS NULL;

NOT EXISTS performs in most straightforward way: just checks equality and returns TRUE or FALSE on the first hit / miss.
LEFT JOIN / IS NULL either makes an additional table look-up or does not return on the first match and performs more poorly in both cases.


Does MERGE updates when not needed? In SQL Server 2012,

Does a merge statement like this :

WHEN MATCHED THEN
update SET A.len = B.len

updates a row even if A.len equals to B.len ?

Or does the SQL SERVER knows to ignore this situations ?

If not, would it be better to add the matching condition the following :

WHEN MATCHED AND A.len <> B.len THEN
update SET A.len = B.len

Is there any difference in this on other Sql servers ?

Thanks,

The short answer is yes. You can read more about the Merge command syntax at the Microsoft MSDN site.


Why does the varchar datatype allow unicode values? I have a table with a varchar column. It is allowing Trademark(™), copyright(©) and other Unicode characters as shown below.

Create table VarcharUnicodeCheck
(
col1 varchar(100)
)

insert into VarcharUnicodeCheck (col1) values ('MyCompany')
insert into VarcharUnicodeCheck (col1) values ('MyCompany™')
insert into VarcharUnicodeCheck (col1) values ('MyCompany░')
insert into VarcharUnicodeCheck (col1) values ('MyCompanyï')
insert into VarcharUnicodeCheck (col1) values ('MyCompany')

select * from VarcharUnicodeCheck

But the definition of varchar says, it allows non-unicode string data. But the Trademark(™) and Registered(®) symbols are Unicode characters. Does the definition contradicts the property of varchar datatype? I read couple of links like first one and second one. But still I could not understand why it allows unicode string when the definition says that it allows only non-unicode string values.

But the Trademark(™) and Registered(®) symbols are Unicode characters.

You are wrong here. Your strings contain only ascii characters.
Here is a simple test that shows you that your characters are all ascii (+ some extended ascii with ascii codes between 128 and 255):
declare @VarcharUnicodeCheck table
(
col1 varchar(100)
)

insert into @VarcharUnicodeCheck (col1) values ('MyCompany')
insert into @VarcharUnicodeCheck (col1) values ('MyCompany™')
insert into @VarcharUnicodeCheck (col1) values ('MyCompany░')
insert into @VarcharUnicodeCheck (col1) values ('MyCompanyï')
insert into @VarcharUnicodeCheck (col1) values ('MyCompany')

select *,
        right(col1, 1)as last_char,
        ascii(right(col1, 1)) as_last_char_ascii
from @VarcharUnicodeCheck;

Here you can clearly see that all your characters are 1-byte encoded:

Yes they are not pure ascii characters but they are Extended ASCII.
Here I show you real Unicode character Trademark(™) and its code and binary representation:
declare @t table (uni_ch nchar(1), ascii_ch char(1));
insert into @t values (N'™', '™');

select unicode(uni_ch) as [unicode of ™],
       ascii(ascii_ch) [ascii of ™],
       cast(uni_ch as varbinary(10)) as [uni_ch as varbinary],
       cast(ascii_ch as varbinary(10)) as [ascii_ch as varbinary]
from @t;

Finally, you can see that Trademark(™) Unicode character has 8482 code and not 153:
select nchar(8482), nchar(153)

Accepting something and ONLY accepting something are NOT the same thing.
If you go to a drive-thru that says "we accept $50 bills", does that mean they ONLY accept $50 bills?   Of course not.  Likewise, neither is accepting Unicode and ONLY accepting Unicode the same.  You added the word "ONLY" where according to your question it did not exist in the definition you quoted.


Using Trigger creates duplicates I have created a trigger to inert into table2 if a new row is inserted into table1 (at least I thought).
The queries is like this:
CREATE TRIGGER project_insert
ON TABLE1
AFTER INSERT
AS
BEGIN
Insert into TABLE2 (ProjectID, Status, Period)
SELECT
    ProjectID
  , Status
  , Period = concat(FORMAT(GETDATE(), 'yyy'), FORMAT(GETDATE(), 'MM'))
FROM TABLE1
WHERE Status in (1, 2)

This seems to work as it is inserting the project after I add it to table1, however everytime I add a new project it seems that the trigger inserts a new row for each project, I have in table1. I would like it to only add the new project.
Is this doable?
Thanks!

You need to use the special virtual table INSERTED which represents the rows that have been inserted causing the trigger to fire and JOIN it back to your Table2 using the PK
(ProjectID in this case I think)
Insert into TABLE2 (ProjectID, Status, Period)
SELECT
    ProjectID
  , Status
  , Period = concat(FORMAT(GETDATE(), 'yyy'), FORMAT(GETDATE(), 'MM'))
FROM TABLE1 T
INNER JOIN INSERTED I ON
  T.ProjectID = i.ProjectID
WHERE Status in (1, 2)

when you didn't change your status, in some way
very row that you enter runs
SELECT
    ProjectID
  , Status
  , Period = concat(FORMAT(GETDATE(), 'yyy'), FORMAT(GETDATE(), 'MM'))
FROM TABLE1
WHERE Status in (1, 2)

and every row would insert all the rows found.
that would be every row in table1 which has status 1 or statis 2, presuming nothing changes the status, it would add the same rows over and over again.
You need to exclude the rows that already exist.
so run something like
CREATE TRIGGER project_insert
ON TABLE1
AFTER INSERT
AS
BEGIN
Insert into TABLE2 (ProjectID, Status, Period)
    SELECT
        ProjectID
      , Status
      , Period = concat(FORMAT(GETDATE(), 'yyy'), FORMAT(GETDATE(), 'MM'))
    FROM TABLE1 t1
    WHERE Status in (1, 2) AND NOT EXISTS(SELECT ProjectID
                    FROM TABLE2 t2
                   WHERE t2.ProjectID = t1.ProjectID)


what is the best practice for tempdb autogrowth? I have a client's server with about 15 databases on this instance with a data total of about 100 GB. One of the databases is for a sysaid app.
his temp db files are one mdf one ndf and of course an ldf.
the mdf is on the same drive as the other mdfs and so if the ldf file.

When I set the autogrowth properties I know I should keep it as unrestricted growth but when I need to choose between autogrowth by % or MB what to choose? and how do I know what number to configure?

Thanks in advance!

For me, the best practice is to just size the files large enough, at the outset, to accommodate the growths you expect.

Let's use some simple numbers. Say you have a 100 GB drive that you've allocated to tempdb (or 100 GB of some shared drive). Your instance has 4 tempdb files, and you've decided to set them initially at 100 MB and to grow 10 MB at a time. Initially, that 100 GB drive or slice looks like this (not to scale, obviously):

As your system uses tempdb resources, each tempdb file will grow, 10 MB by agonizing 10 MB. After a few growths:

Note that each growth event, while likely fast-ish, forces all connections to wait. This is more pronounced when Instant File Initialization is not enabled, and if the files are configured to grow together (which requires a trace flag in some versions, but this is the right thing to do, because you don't want any single file to be bigger than the others).

When you get to the end of the drive, all 4 files will fail to grow at the same time. And this is a business continuity event. When tempdb can't grow here, activity halts:

Now, if you're getting to this point, ever, you need to acknowledge that either you need to change the way you use tempdb, or 100 GB isn't enough and you need to allocate more space. But I would argue that it's better to simply start here and configure this size for your tempdb initial data file sizes:

Well, technically, I would size them at, say, 20 GB each, with a 500 MB autogrowth. This allows you to still be warned well in advance of filling up the 100GB slice, but without paying for all of those incremental file growths in the meantime.

After all, if tempdb is going to grow to fill that space, it's not like you can use it for anything else.

  which size should i give the autogrowth

There is no magic number (the defaults have been traditionally way too conservative for practical use, but this is getting better). Your autogrowth setting will be based on the performance characteristics of your disk, and how often you expect to grow. If you have SSD or similar, and have IFI enabled, size is probably much more tied to how much warning you want to have before hitting the end of the slice. If you have spinny, mechanical drives, and/or don't have IFI enabled, you will probably want to test various settings and see where the time it takes to grow the file by different amounts meets your tolerance for all transactions on the system to wait.

You should not always set auto growth to unrestricted.  If something is wrong you don't want to fill up the disk.  And you want a heads up that something is not going as you expected.   Pick a big number that you don't think you would need if things are going as expected.

It takes time to create and grow.  If you start at 20GB less total time is taken compare to start at 10GB and grow by 10GB once.

Pick a starting number that will fit your normal needs.  You can start small and see how big it quickly grows to in normal operation.

A fixed number of 10GB would be 9 growths to get to 100GB.  If you double (100%) would be 4 growths to get to 160GB.  I usually pick like 10-40%.

You get some fragmentation when you grow but that is not much of a factor unless you are growing a lot (like 20+).

If you have more physical drives then it is typically best to put it on another drive to level out traffic.  TempDB has little to no need for redundancy.


MySQL database datafiles in windows SystemData directory When I create a database and some tables in MySQL Server, it makes some data files in Windows SystemData directory in C:\ProgramData\MySQL\MySQL Server 5.5\data\mydb\.

Each table has three files with these suffixes:

TableName.frm
TableName.MYD
TableName.MYI

I don't know what those exactly are.

Please explain to me what those are and what is usages?

Thanks in advance.

Those three files represent one MyISAM table

For the MyISAM table, mydb.TableName, here is what they are

TableName.frm : Table format file (all Storage Engines have this one)
TableName.MYD : MyISAM data for the table
TableName.MYI : MyISAM Indexes for the Table

Click to read MySQL Documentation for MyISAM Storage Engine. In part, it says:

  Each MyISAM table is stored on disk in three files. The files have names that begin with the table name and have an extension to indicate the file type. An .frm file stores the table format. The data file has an .MYD (MYData) extension. The index file has an .MYI (MYIndex) extension.

MyISAM is the historical storage engine of the MySQL server on the hard disk, a MyISAM table is represented by three files:

table_name.frm which contains the structure of the table
table_name.MYD containing data.
table_name.MYI containing the index

Source : MySQL 5.6
Administartion et optimisation
Edition ENI


SQL Server Concise List Of Trace Flags I did my 5 minute research and have not come up with a concise list, or even a list really except for Trace Flags (2012) which is a bit lacking.

Looking at this query which uses 8691, I was wondering if such a list exists.

with cte(guid,other) as (
select newid(),1 union all
select newid(),2 union all
select newid(),3)
select a.guid, a.other, b.guid guidb, b.other otherb
from cte a
cross join cte b
order by a.other, b.other
OPTION (QUERYTRACEON 8691);

http://antapex.org/traceflags_sqlserver.txt

Although this isn't complete, it is the biggest single list I know of.

This MSDN article lists the Query Optimizer Trace Flags necessary to support several changes to the QO in various QFEs and hotfixes, etc.

http://support.microsoft.com/kb/974006

Technet also has a list of various trace flags (probably already documented in the links above:

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

This documents trace flag 610:  http://msdn.microsoft.com/en-us/library/dd425070%28v=SQL.100%29.aspx (controls minimally logged inserts into indexed tables)

Technet has another huge list of trace flags at: http://social.technet.microsoft.com/wiki/contents/articles/13105.trace-flags-in-sql-server.aspx

I thought I'd create a table for quick reference.

CREATE TABLE dbo.TraceDefinition (
 TraceFlag INT NOT NULL CONSTRAINT PK_TradeDefinition PRIMARY KEY CLUSTERED,
 [Description] VARCHAR(MAX) NOT NULL
)

INSERT INTO dbo.TraceDefinition (TraceFlag, [Description])
SELECT TraceFlag, [Description]
FROM (VALUES (260, 'Prints versioning information about extended stored procedure dynamic-link libraries (DLLs). For more information about __GetXpVersion(), see Creating Extended Stored Procedures. Scope: global or session'),
        (1204,'Returns the resources and types of locks participating in a deadlock and also the current command affected. Scope: global only'),
        (1211,'Disables lock escalation based on memory pressure, or based on number of locks. The SQL Server Database Engine will not escalate row or page locks to table locks. Using this trace flag can generate excessive numbers of locks. This can slow the performance of the Database Engine, or cause 1204 errors (unable to allocate lock resource) because of insufficient memory. For more information, see Lock Escalation (Database Engine). If both trace flag 1211 and 1224 are set, 1211 takes precedence over 1224. However, because trace flag 1211 prevents escalation in every case, even under memory pressure, we recommend that you use 1224. This helps avoid "out-of-locks" errors when many locks are being used. Scope: global or session'),
        (1222,'Returns the resources and types of locks that are participating in a deadlock and also the current command affected, in an XML format that does not comply with any XSD schema. Scope: global only'),
        (1224,'Disables lock escalation based on the number of locks. However, memory pressure can still activate lock escalation. The Database Engine escalates row or page locks to table (or partition) locks if the amount of memory used by lock objects exceeds one of the following conditions: Forty percent of the memory that is used by Database Engine, exclusive of memory allocation using Address Windowing Extension (AWE). This is applicable only when the locks parameter of sp_configure is set to 0. Forty percent of the lock memory that is configured by using the locks parameter of sp_configure. For more information, see Setting Server Configuration Options. If both trace flag 1211 and 1224 are set, 1211 takes precedence over 1224. However, because trace flag 1211 prevents escalation in every case, even under memory pressure, we recommend that you use 1224. This helps avoid "out-of-locks" errors when many locks are being used. Note: Lock escalation to the table- or HoBT-level granularity can also be controlled by using the LOCK_ESCALATION option of the ALTER TABLE statement. Scope: global or session'),
        (2528,'Disables parallel checking of objects by DBCC CHECKDB, DBCC CHECKFILEGROUP, and DBCC CHECKTABLE. By default, the degree of parallelism is automatically determined by the query processor. The maximum degree of parallelism is configured just like that of parallel queries. For more information, see max degree of parallelism Option. Parallel DBCC should typically be left enabled. For DBCC CHECKDB, the query processor reevaluates and automatically adjusts parallelism with each table or batch of tables checked. Sometimes, checking may start when the server is almost idle. An administrator who knows that the load will increase before checking is complete may want to manually decrease or disable parallelism. Disabling parallel checking of DBCC can cause DBCC to take much longer to complete and if DBCC is run with the TABLOCK feature enabled and parallelism set off, tables may be locked for longer periods of time. Scope: global or session'),
        (3205,'By default, if a tape drive supports hardware compression, either the DUMP or BACKUP statement uses it. With this trace flag, you can disable hardware compression for tape drivers. This is useful when you want to exchange tapes with other sites or tape drives that do not support compression. Scope: global or session'),
        (3226,'By default, every successful backup operation adds an entry in the SQL Server error log and in the system event log. If you create very frequent log backups, these success messages accumulate quickly, resulting in huge error logs in which finding other messages is problematic. With this trace flag, you can suppress these log entries. This is useful if you are running frequent log backups and if none of your scripts depend on those entries.'),
        (3608,'Prevents SQL Server from automatically starting and recovering any database except the master database. Databases will be started and recovered when accessed. Some features, such as snapshot isolation and read committed snapshot, might not work. Use for Moving System Databases and Moving User Databases. Do not use during normal operation.'),
        (3625,'Limits the amount of information returned in error messages. For more information, see Metadata Visibility Configuration. Scope: global only'),
        (4616,'Makes server-level metadata visible to application roles. In SQL Server, an application role cannot access metadata outside its own database because application roles are not associated with a server-level principal. This is a change of behavior from earlier versions of SQL Server. Setting this global flag disables the new restrictions, and allows for application roles to access server-level metadata. Scope: global only'),
        (6527,'Disables generation of a memory dump on the first occurrence of an out-of-memory exception in CLR integration. By default, SQL Server generates a small memory dump on the first occurrence of an out-of-memory exception in the CLR. The behavior of the trace flag is as follows: If this is used as a startup trace flag, a memory dump is never generated. However, a memory dump may be generated if other trace flags are used. If this trace flag is enabled on a running server, a memory dump will not be automatically generated from that point on. However, if a memory dump has already been generated due to an out-of-memory exception in the CLR, this trace flag will have no effect. Scope: global only'),
        (7806,'Enables a dedicated administrator connection (DAC) on SQL Server Express. By default, no DAC resources are reserved on SQL Server Express. For more information, see Using a Dedicated Administrator Connection.')
) AS t(TraceFlag,[Description])


Do SQL OS Background Tasks Yield to Normal Tasks? I have a SQL Server 2019 server that is running at about 22% CPU.  I look at what's running and I see that about half of that 22% is coming from background tasks (i.e. the status is "background") such as QUERY STORE APRC, GHOST CLEANUP, and TASK MANAGER.
So, if the server were to become busy running queries for the application, would those background tasks receive a lower priority or a smaller quantum or something that would see them give back the majority of the CPU they are using?
Basically, I keep getting asked what's using the CPU and I answer that about 50% of the CPU usage comes from background tasks.  Then I start getting asked how to turn them off.  If I can say that the background tasks are safe to ignore, I can make that stop.  Unfortunately, I have not been able to find anything definitive to support that assertion.

The background tasks are there for a purpose & are generally necessary so everything else being equal I wouldn't try and turn them off. I believe that some of them at least do act as you describe and reduce their pressure when CPU load is high - ie Query Store.
A detailed answer to this question would be level 500 internals and extremely long - probably better to wait until your CPU is stuck with a way higher load than 22%. There is undoubtedly lower hanging fruit to optimize


Find all IP's or users who access a specific SQL Server Let's say I found a SQL Server and I have no idea which applications connect to it. Maybe I find one application but I am not sure if it is the only one using it.

Is there a nice way of finding all distinct connections?

You can look at the currently running processes in SSMS in Activity Monitor.

You can also get it using sys.dm_exec_sessions as well as sys.dm_exec_connections and sys.dm_exec_requests.

Query:

SELECT DB_NAME(database_id) as [DB]
    , login_name
    , nt_domain
    , nt_user_name
    , status
    , host_name
    , program_name
    , COUNT(*) AS [Connections]
FROM sys.dm_exec_sessions
WHERE database_id > 0 -- OR 4 for user DBs
GROUP BY database_id, login_name, status, host_name, program_name, nt_domain, nt_user_name;

Hostname, process name or domain user names often give more information than just an IP address. Other columns can be added such as: login_time, last_successful_logon

IP and other information can be retrieved with a JOIN to sys.dm_exec_connections on session_id: client_net_address, local_net_address, connect_time, ports, ...

sys.dm_exec_requests can also give useful information: command type, sql_handle, ...

This query is just a sample. You must JOIN these 3 views together and output/store relevant information from either of them.

Data from these view disapear when SQL Server is restarted. Therefore if the application is not regularly connected it may be a good idea to store them on a regular basis using a job or a script (Powershell).

From Get Client IP Address in SQL Server by Sarvesh Kumar Gupta:

CREATE FUNCTION [dbo].[GetCurrentIP] ()
RETURNS varchar(255)
AS
BEGIN
    DECLARE @IP_Address varchar(255);

   SELECT @IP_Address = client_net_address
    FROM sys.dm_exec_connections
    WHERE Session_id = @@SPID;

   Return @IP_Address;
END


MySQL Index for searching for NULL values in text fields I need to do the simple query SELECT id FROM table WHERE blob_field IS NULL.

blob_field is a longblob. If I create an index on blob_field with prefix length 1, will this be sufficient to use for this query?

Yes, a prefix length of one will allow you to have a working index for looking up NULLs.

Here's the SQLFiddle: http://sqlfiddle.com/#!2/12bf3/1/0

The EXPLAIN says that it is using the desired index.

Of course you might want to try a larger test to make sure it performs as well in reality as you hope.

Why don't you do like this :

SELECT OCTET_LENGTH(blob_field ) FROM table WHERE predicate ;


Oracle 12c RAC Cluster / CRS fails to start after reboot Could someone shed some light to help fix the cluster/CRS startup failure

1)
/oragrid/app/grid_home/bin/crsctl start crs

CRS-4640: Oracle High Availability Services is already active

CRS-4000: Command Start failed, or completed with errors.

2)
/oragrid/app/grid_home/bin/crsctl check crs

CRS-4638: Oracle High Availability Services is online

CRS-4535: Cannot communicate with Cluster Ready Services

CRS-4530: Communications failure contacting Cluster Synchronization Services
daemon

CRS-4534: Cannot communicate with Event Manager

3)
/oragrid/app/grid_home/bin/crsctl start res ora.crsd -init

CRS-2672: Attempting to start 'ora.gpnpd' on 'xxxxxxxxxx'

CRS-2674: Start of 'ora.gpnpd' on 'xxxxxxxxxx' failed

4)
/oragrid/app/grid_home/bin/crsctl start cluster

CRS-2672: Attempting to start 'ora.gpnpd' on 'xxxxxxxx'

CRS-2674: Start of 'ora.gpnpd' on 'xxxxxxxx' failed

ASM manual startup:

[oracle@rac1 ~]$ cd /u01/app/11.2.0/grid/bin
[oracle@rac1 bin]$ ./crsctl check crs
CRS-4639: Could not contact Oracle High Availability Services
[oracle@rac1 bin]$
[oracle@rac1 bin]$ su - root
Password:
[root@rac1 ~]# cd /u01/app/11.2.0/grid/bin
[root@rac1 bin]# ./crsctl start crs
CRS-4124: Oracle High Availability Services startup failed
CRS-4000: Command Start failed, or completed with errors
[root@rac1 bin]#

[root@rac1 bin]# ./crsctl config has
CRS-4622: Oracle High Availability Services autostart is enabled.
[root@rac1 bin]#

[root@rac1 bin]# nohup /etc/init.d/init.ohasd run &
press Enter:
[root@rac1 bin]# ./crsctl check crs
CRS-4638: Oracle High Availability Services is online
CRS-4535: Cannot communicate with Cluster Ready Services
CRS-4530: Communications failure contacting Cluster Synchronization Services daemon
CRS-4534: Cannot communicate with Event Manager

[root@rac1 bin]# cd /dev/oracleasm/disks
[root@rac1 disks]# ls -lrt
total 0
brw------- 1 root root 8, 17 May  6 10:16 DISK1
brw------- 1 root root 8, 33 May  6 10:16 DISK2
brw------- 1 root root 8, 49 May  6 10:16 DISK3
brw------- 1 root root 8, 65 May  6 10:16 DISK4
brw------- 1 root root 8, 81 May  6 10:16 DISK5

[root@rac1 bin]# ps -ef  | grep css
root      3784     1  0 10:17 ?        00:00:01 /u01/app/grid/11.2.0/bin/cssdmonitor
root      3801     1  0 10:17 ?        00:00:01 /u01/app/grid/11.2.0/bin/cssdagent
root      4189  4107  0 10:26 pts/1    00:00:00 grep css

[root@rac1 disks]# chown -R oracle:dba /dev/oracleasm/disks
[root@rac1 disks]# chmod -R 777 /dev/oracleasm/disks
[root@rac1 disks]# ls -lrt
total 0
brwxrwxrwx 1 oracle dba 8, 17 May  6 10:16 DISK1
brwxrwxrwx 1 oracle dba 8, 33 May  6 10:16 DISK2
brwxrwxrwx 1 oracle dba 8, 49 May  6 10:16 DISK3
brwxrwxrwx 1 oracle dba 8, 65 May  6 10:16 DISK4
brwxrwxrwx 1 oracle dba 8, 81 May  6 10:16 DISK5

[root@rac1 bin]# ./crsctl start cluster
CRS-2672: Attempting to start 'ora.cssd' on 'rac1'
CRS-2676: Start of 'ora.cssd' on 'rac1' succeeded
CRS-2672: Attempting to start 'ora.ctssd' on 'rac1'
CRS-2676: Start of 'ora.ctssd' on 'rac1' succeeded
CRS-2679: Attempting to clean 'ora.asm' on 'rac1'
CRS-2681: Clean of 'ora.asm' on 'rac1' succeeded
CRS-2672: Attempting to start 'ora.asm' on 'rac1'
CRS-2676: Start of 'ora.asm' on 'rac1' succeeded
CRS-2672: Attempting to start 'ora.crsd' on 'rac1'
CRS-2676: Start of 'ora.crsd' on 'rac1' succeeded

[root@rac1 bin]# ps -ef | grep css
root      3784     1  0 10:17 ?        00:00:01 /u01/app/grid/11.2.0/bin/cssdmonitor
root      4372     1  0 10:30 ?        00:00:01 /u01/app/grid/11.2.0/bin/cssdagent
oracle    4387     1  1 10:30 ?        00:00:02 /u01/app/grid/11.2.0/bin/ocssd.bin
root      5347  4107  0 10:33 pts/1    00:00:00 grep css

[root@rac1 bin]# ./crsctl check crs
CRS-4638: Oracle High Availability Services is online
CRS-4537: Cluster Ready Services is online
CRS-4529: Cluster Synchronization Services is online
CRS-4533: Event Manager is online
[root@rac1 bin]#

Su – oracle

Srvctl start database –d dbname

Create diskgroup FRA external redundancy ‘ORCL:VOL5’;(in ASM (sqlplus / as sysasm))

Asmcmd lsdg
Alter diskgroup FRA mount; (if not in mount).

http://mbc-dba.blogspot.com/2014/08/crs-4639-crs-4124-oracle-high.html
https://shivanandarao-oracle.com/2012/06/03/crs-4535-cannot-communicate-with-cluster-ready-services/


Lossy Decomposition meaning Suppose I have a relation R=(A,B,C,D) and I decompose it into:

R1=(A,B,C)
R2=(A,D)

then if it is said that the decomposition is lossy,then what is the meaning of that statement?I have an understanding that the decomposition is lossy iff:

R1 ∪ R2=R

But can't see the meaning of this here because schemas are not same so we cannot take union.

As this is a loss less join thus in lossless join we know that on joining we can get original relation along with some extra tuples thus answere is r⊂S where r is the relation in which original reltions were decomposed.


Why would a primary key value change? I have been researching the concept of ROWGUID's recently and came across this question.  This answer gave insight, but has led me down a different rabbit hole with the mention of changing the primary key value.

My understanding has always been that a primary key should be immutable, and my searching since reading this answer has only provided answers which reflect the same as a best practice.

Under what circumstances would a primary key value need to be altered after the record is created?

If you were using a person's name as a primary key and their name changed you would need to change the primary key.  This is what ON UPDATE CASCADE is used for since it essentially cascades the change down to all related tables that have foreign-key relationships to the primary key.

For example:

USE tempdb;
GO

CREATE TABLE dbo.People
(
    PersonKey VARCHAR(200) NOT NULL
        CONSTRAINT PK_People
        PRIMARY KEY CLUSTERED
    , BirthDate DATE NULL
) ON [PRIMARY];

CREATE TABLE dbo.PeopleAKA
(
    PersonAKAKey VARCHAR(200) NOT NULL
        CONSTRAINT PK_PeopleAKA
        PRIMARY KEY CLUSTERED
    , PersonKey VARCHAR(200) NOT NULL
        CONSTRAINT FK_PeopleAKA_People
        FOREIGN KEY REFERENCES dbo.People(PersonKey)
        ON UPDATE CASCADE
) ON [PRIMARY];

INSERT INTO dbo.People(PersonKey, BirthDate)
VALUES ('Joe Black', '1776-01-01');

INSERT INTO dbo.PeopleAKA(PersonAKAKey, PersonKey)
VALUES ('Death', 'Joe Black');

A SELECT against both tables:

SELECT *
FROM dbo.People p
    INNER JOIN dbo.PeopleAKA pa ON p.PersonKey = pa.PersonKey;

Returns:

If we update the PersonKey column, and re-run the SELECT:

UPDATE dbo.People
SET PersonKey = 'Mr Joe Black'
WHERE PersonKey = 'Joe Black';

SELECT *
FROM dbo.People p
    INNER JOIN dbo.PeopleAKA pa ON p.PersonKey = pa.PersonKey;

we see:

Looking at the plan for the above UPDATE statement, we clearly see both tables are updated by a single update statement by virtue of the foreign key defined as ON UPDATE CASCADE:

click the image above to see it in more clarity

Finally, we'll cleanup our temporary tables:

DROP TABLE dbo.PeopleAKA;
DROP TABLE dbo.People;

The preferred1 way to do this using surrogate keys would be:

USE tempdb;
GO

CREATE TABLE dbo.People
(
    PersonID INT NOT NULL IDENTITY(1,1)
        CONSTRAINT PK_People
        PRIMARY KEY CLUSTERED
    , PersonName VARCHAR(200) NOT NULL
    , BirthDate DATE NULL
) ON [PRIMARY];

CREATE TABLE dbo.PeopleAKA
(
    PersonAKAID INT NOT NULL IDENTITY(1,1)
        CONSTRAINT PK_PeopleAKA
        PRIMARY KEY CLUSTERED
    , PersonAKAName VARCHAR(200) NOT NULL
    , PersonID INT NOT NULL
        CONSTRAINT FK_PeopleAKA_People
        FOREIGN KEY REFERENCES dbo.People(PersonID)
        ON UPDATE CASCADE
) ON [PRIMARY];

INSERT INTO dbo.People(PersonName, BirthDate)
VALUES ('Joe Black', '1776-01-01');

INSERT INTO dbo.PeopleAKA(PersonID, PersonAKAName)
VALUES (1, 'Death');

SELECT *
FROM dbo.People p
    INNER JOIN dbo.PeopleAKA pa ON p.PersonID = pa.PersonID;

UPDATE dbo.People
SET PersonName = 'Mr Joe Black'
WHERE PersonID = 1;

For completeness, the plan for the update statement is very simple, and shows one advantage to surrogate keys, namely only a single row needs to be updated as opposed to every row containing the key in a natural-key scenario:

SELECT *
FROM dbo.People p
    INNER JOIN dbo.PeopleAKA pa ON p.PersonID = pa.PersonID;

DROP TABLE dbo.PeopleAKA;
DROP TABLE dbo.People;

The output from the two SELECT statements above are:

Essentially, the outcome is approximately the same.  One major difference is the wide natural key is not repeated in every table where the foreign key occurs.  In my example, I'm using a VARCHAR(200) column to hold the person's name, which necessitates using a VARCHAR(200) everywhere.  If there are a lot of rows and a lot of tables containing the foreign key, that will add up to a lot of wasted memory.  Note, I'm not talking about disk space being wasted since most people say disk space is so cheap as to be essentially free.  Memory, however, is expensive and deserves to be cherished.  Using a 4-byte integer for the key will save a large amount of memory when you consider the average name length of around 15 characters.

Tangential to the question about how and why keys can change is the question about why to choose natural keys over surrogate keys, which is an interesting and perhaps more important question, especially where performance is a design-goal.  See my question here about that.

1 - http://weblogs.sqlteam.com/mladenp/archive/2009/10/06/Why-I-prefer-surrogate-keys-instead-of-natural-keys-in.aspx

Imagine a situation like when someone chose National Insurance Number (NIN) as a Primary Key and somehow an operator inserts a row with the wrong NIN. After inserting the value, there are two ways to correct the error:

Delete the mistaken record and insert a new one
Update the value to the correct one and use On Update Cascade if there is a referential integrity constraint on that column


How do I get SQL Server agent to show up in SSMS for a non-sysadmin user? I have already mapped the user to the msdb database and assigned him the role of SQLAgentOperatorRole, but the user still can't see the SQL Server Agent listed when he connects.

The SQL Server Agent does show up fine when I log in as a sysadmin.

There are several points to consider.

Make sure that person connecting to SQL Server 2012 is using SSMS 2012, I have seen compatibility issues when some features are not showing up when connecting with wrong version.
Remove all roles from the user and grant them again, go over these steps to make sure that you have done it correctly first time.

Script for creating account and assigning role:

USE [master]
CREATE LOGIN SQlSATest WITH PASSWORD = 'test', CHECK_EXPIRATION=OFF, CHECK_POLICY= OFF;
USE [msdb]
CREATE USER [SQLSATest] FROM LOGIN SQLSATest;
EXECUTE sp_addrolemember @rolename = 'SQLAgentOperatorRole', @membername = 'SQLSATest'

Page https://docs.microsoft.com/en-us/sql/ssms/agent/sql-server-agent-fixed-database-roles says "A user must be a member of one of these fixed database roles or a member of the sysadmin fixed server role to use SQL Server Agent."

So you SHOULD not need to be a member of the sysadmin role to see SQL Server Agent.  Other questions on SA say that sysadmin is required though.  Maybe it's a SQL Server or SSMS bug...


Database design of Room Booking system in a Hotel Intro and System Description

I am currently designing a Database for a Room Booking system in a Hotel.

Client fills a Form / Request with following information about Room:

amount of people in room
rating of a room
check in and check out date

Administrator has a dashboard with list of Forms from Clients, he will assign each Room for every Client manually.
After this User gets a Bill.

Database Design

This is actually a Sketch of my DB. I'll have following tables:

Users
Forms
Rooms
Bills

I am not considering table Passwords which contains Password's Hashes.

Questions

I would like to hear from you what is missing in my DB Design, what do you think about overall logic and correctness of relations between tables.

Without knowing exact and whole requirements we cannot design a complete data model. Let us assume the following business rules, it is based on your question.

Each Customer can request for one or more Room.
Each Room can be requested by one Customer
Each Admin can assign one or many Room.
Each Room must be assigned by one Admin.
Each Room must be belongs to one Room Type.
Each Room Type can contain one or many Room.
Each Booking must be belongs to one Date.
Each Date can contain one or more Booking.
Each Room Type can contain one or many Rent.
Each Rent must belongs to one Room Type.
Each Booking must include one or many Room.
Each Room can be included in one Booking.
Each Customer can hold one or many Booking.
Each Booking must belongs to one Customer.
Each Rent can be included in one or many Bill.
Each Bill must include one Rent.
Each Bill Payment must include one Payment Type.
Each Payment Type can contain one or many Bill Payment.
Each Date can contain one or many Bill Payment.
Each Bill Payment must belongs to one Date.
Each Room can be rated one or many Room Rating.
Each Room Rating must belongs to one Room.
Each Rating can contain one or many Room Rating.
Each Room Rating must belongs to one Rating.

Based on the above rules we have designed the following data model.

This model is a subset of Hotel Booking system. After the normalization process (check my blog), you will have more detailed and complete model.
In the above model Room rent is calculated based on the room type such as Single Room, Doubles Room, Family Room or Meeting Hall rather than number of people in the room.
And Room rent is changed from time to time, so we have included the from date and to date to have history of room rents. Also customer can use many payment types such as credit card, debit card, cash payment, so we are included payment type entity.
This model is based on assumption. I hope this model will help you in someway.
Thank you.

We’ll focus on the following set of requirements while designing the Hotel Management System:

The system should support the booking of different room types like standard, deluxe, family suite, etc.

Guests should be able to search the room inventory and book any available room.

The system should be able to retrieve information, such as who booked a particular room, or what rooms were booked by a specific customer.

The system should allow customers to cancel their booking and provide them with a full refund if the cancellation occurs before 24 hours of the check-in date.

The system should be able to send notifications whenever the booking is nearing the check-in or check-out date.

The system should maintain a room housekeeping log to keep track of all housekeeping tasks.

Any customer should be able to add room services and food items.
Customers can ask for different amenities.
The customers should be able to pay their bills through credit card, check or cash.

Here is a class diagram for hotel booking system. Hope this helps.


while restoring mysql database, views changed into tables I have a full database dump and i restored it. But in my new restored server all the views are appeared as MyISAM tables. In early times dumps were restored perfectly. can anyone help me to trigger out the issue, please?

Regards,
Praveen

In the backup script the views are first created as tables which are then dropped at the end of the script as each view is being created, so it seems that an error occurs while creating the views at the end of the script.

However when a view is created there is a user who is used in the DEFINER clause of the view who may not exist in your database, e.g., DEFINER=user123@192.168.1.%

I usually remove that clause in the backup script and it tends to work.

To verify add the -v option when importing the database

I replaced hostname 'localhost' with'%' and it worked...
I was trying to restore database using command prompt. while dropping table it was showing error 1227 which is related to user privileges. since the data was dumped from my local machine user credentials were also dumped in the file.


Cannot change MySQL root password I have run the following commands found here.

shell> mysql
mysql> FLUSH PRIVILEGES;
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'MyNewPass'

But when I go to login as root, I can type any string as a password, and it will still let me login.

How can I enforce a password for root, and not allow anonymous users by simply typing mysql

The following worked for me:

I ran through the command line wizard to set this up:
shell> mysql_secure_installation

Then entered the following:
shell> mysql
ALTER USER 'root'@'localhost' IDENTIFIED with mysql_native_password;
ALTER USER 'root'@'localhost' IDENTIFIED BY 'password';

Credit goes to this question/answer.

You wanna try this granting all users by password

Grant All Privileges ON *.* to 'USER_NAME'@'%' Identified By 'YOUR_PASSWORD';

FLUSH PRIVILEGES;


Low PLE on NUMA node 000, high on 001 I'm looking at PLE (Page Life Expectancy) across NUMA nodes on our SQL Servers, and came across a rather weird distribution. NUMA node 000 has a very low PLE compared to 001. I'm unsure why this is. I've checked on multiple other SQL Servers in our environment, and the other production servers do not have this behavior.

System is running SQL Server 2012 Enterprise Edition on Dell m620 with 256GB Ram. It's a 2 socket, 6 core (HT enabled) machine. MAXDOP is set at 6. AFAIK memory modules are installed evenly across the memory banks of the CPUs

Something tells me NUMA node 000 has other SQL tasks to perform, that other nodes, but i've forgotten where I heard/saw it.

@@Version shows: Microsoft SQL Server 2012 (SP1) - 11.0.3412.0 (X64)

If you have a read-intensive query running on one NUMA node (in this case, 0), then it can experience a lower page life expectancy relative to other NUMA nodes.

That's totally normal.

To see what queries are running right now, you can use Adam Machanic's excellent sp_WhoIsActive. It's totally free. Some folks even run it every X minutes and log the data into a table so they can go back to see what was running at the time PLE nosedived.

My understanding of NUMA architecture is that each node pretty much isolates itself. In that case they could end up doing very different work. For example, 0 could be executing queries requiring lots of physical I/O while 1 gets lucky and finds all its data in the buffer pool.


What are the main causes of deadlocks and can they be prevented? Recently one of our ASP.NET applications displayed a database deadlock error and I was requested to check and fix the error. I managed to find the cause of the deadlock was a stored procedure that was rigorously updating a table within a cursor.

This is the first time I've seen this error and didn't know how to track and fix it effectively.  I tried all the possible ways I know, and finally found that the table which is being updated doesn't have a primary key! luckily it was an identity column.

I later found the developer who scripted database for deployment messed-up. I added a primary key and the problem was solved.

I felt happy and came back to my project, and did some research to found out the reason for that deadlock...

Apparently, it was a circular wait condition that caused the deadlock. Updates apparently take longer without a primary key than with primary key.

I know it isn't a well defined conclusion, that is why I'm posting here...

Is the missing primary key the problem?
Are there any other conditions which cause deadlock other than (mutual exclusion, hold and wait, no preemption and circular wait)?
How do I prevent and track deadlocks?

tracking deadlocks is the easier of the two:

  By default, deadlocks are not written in the error log. You can cause SQL to write deadlocks to the error log with trace flags 1204 and 3605.

  Write deadlock info to the SQL Server error log:
  DBCC TRACEON(-1, 1204, 3605)

  Turn it off:
  DBCC TRACEOFF(-1, 1204, 3605)

  See "Troubleshooting Deadlocks" for a discussion of trace flag 1204 and the output you will get when it is turned on.
  https://msdn.microsoft.com/en-us/library/ms178104.aspx

Prevention is more difficult, essentially you have to look out for the following:

Code Block 1 locks resource A, then resource B, in that order.

Code Block 2 locks resource B, then resource A, in that order.

This is the classic condition where a deadlock can occur, if the locking of both the resources is not atomic, the Code Block 1 can lock A and be pre-empted, then Code Block 2 locks B before A gets processing time back.  Now you have deadlock.

To prevent this condition, you can do something like the following

Code Block A (psuedo code)

Lock Shared Resource Z
    Lock Resource A
    Lock Resource B
Unlock Shared Resource Z
...

Code Block B (pseudo code)

Lock Shared Resource Z
    Lock Resource B
    Lock Resource A
Unlock Shared Resource Z
...

not forgetting to unlock A and B when done with them

this would prevent the deadlocking between code block A and code block B

From a database perspective, I'm not sure on how to go about preventing this situation, as locks are handled by the database itself, i.e. row/table locks when updating data.  Where I've seen the most issues occur is where you saw yours, inside a cursor.  Cursors are notoriously inefficient, avoid them if at all possible.

While cursors are slow in SQL Server, you can avoid deadlocking in a cursor by pulling the source data for the cursor into a Temp table and running the cursor on it.  This keeps the cursor from locking the actual data table and the only locks you get are for the updates or inserts performed inside the cursor which are only held for the duration of the insert/update and not for the duration of the cursor.