Hot answers tagged

9

I think sp_Blitz by good folks at Brent Ozar Unlimited is the best way to go with a new to me server. https://www.brentozar.com/blitz/ https://www.brentozar.com/first-aid/sql-server-downloads/


4

You have commas between the column type and setting its nullability. You need to use spaces between the column options and commas to delimit the column list. Also the nullability option is two words NOT NULL and for an NVARCHAR type you need to specify a length otherwise it will just be a size of one character. I've made the needed updates to what you've ...


4

There really is no "ideal" Fill Factor since there are too many other factors to consider. What is the actual page size of the table? How many variable length columns are in the table? If not "none, then: How likely / often are the variable length fields updated to a length greater than their initial value? If you have a table with a row size of 3000 ...


4

The point of "default" values is that they are the default values. You Don't need to list them in the insert query at all. They'll be inserted automatically. To pass CURRENT_TIMESTAMP as a parameter, you probably need to use the equivalent pseudo-function, CURRENT_TIMESTAMP()... which is the same as NOW() Stop and consider DEFAULT CURRENT_TIMESTAMP ON ...


3

From comments: DISTINCT is very similar to GROUP BY <all selected columns> and getting rid of the temporary table may be impossible when joining many tables as the server needs it to check uniqueness of the returned rows. Covering indexes (Using index) are quite useful when you need to get the top performance. I prefer IN() instead of multiple ORs ...


3

You move tables, not indexes. You rebuild indexes. However, when you rebuild indexes, you can specify a tablespace, not a datafile, so no difference in that aspect. Since you have to rebuild the indexes anyway, instead of rebuilding them immediately (and not having control in which datafile the new extents will be placed), you can just simply make the ...


2

Your query has many issues that difficult optimisation. You use LEFT JOIN in several tables where INNER JOIN should suffice (e.g. all cases of ged_tbl_document_dv and ged_tbl_document_type). You include tables that you later never really require (e.g. all cases of ged_tbl_document_type), so removing all of them wouldn't impact the result (replace all ...


2

It's better to extract some rarely used fields to another table. Especially if those fields are big (blob, long varchar etc). This will case smaller row size and better optimized buffer usage. Because small data (i.e 'id', 'name', 'status') will be stored in buffer and will have fast access to it. And queries with big fields (i.e. 'description TEXT') will be ...


2

Just looking at an example of a table is a wrong way of normalizing. You should understand the meaning of the fields to properly normalize. For instance you should know if the salary is the same for all the persons that have a certain role, or if it differs from person to person (in the above example we cannot know, since each role has only a person, so that ...


2

assuming licensing is not an issue, security and compatibility can be easily grouped and managed (very small user base, very small lightweight databases) You already have addressed the concerns. IMHO, depending on the VM capacity (memory, CPU and disk space), you should consolidate databases to one instance. This gives you advantage of managing one ...


2

Oracle on Windows creates a service for database instances, called OracleService%ORACLE_SID%. By default, this service uses the Local System account so a password change of the installation user will not affect the database. It is however possible and recommended to change the service user from Local System to a standard Windows account. If you performed ...


1

To answer your question simply, if you have 1 VM with several instances, you have the possibility of resource contention between the instances on that VM. In that scenario, I would consolidate databases onto a singular instance. But if you're talking about multiple virtual servers with one instance per virtual machine, I'm going to go against the grain here ...


1

Considerations: How much disk space/memory can you afford? Lower fill factor will mean large indexes. Can you measure the current performance impact and adjust based on Page Life Expectancy and Page Splits? (A maintenance solution like Ola Hallengren's scripts will provide a table that logs rebuild operations) Keep in mind that even though the update ...


1

There are a number of reasons as to why it is advisable to have your database exist on a separate server to your web application. In my opinion chief amongst these are the security implications. For instance a simplistic network architecture would see your web server in a DMZ, or public subnet, and your database server in a private subnet. The web server ...


1

I know it's essentially a comic, but I keep it on my shelf for reference and to start conversations. If you want something to talk about database normalization from scratch with good examples, I recommend "The Manga Guide to Databases by Mana Takahashi". http://amzn.com/1593271905


1

Beginning Database Design was suggested in High Performance MySQL. I've found this book to be a good, short enough to be to the point, introduction to database theory.


1

Each hour: CREATE EVENT reset ON SCHEDULE EVERY 1 HOUR DO UPDATE admin set aLoginAttempts = 0


1

Looks like your oracle directory doesn't exists. You will need to log into the database, create a directory, and retry your import export ORACLE_HOME=/path/to/oracle/home export ORACLE_SID=YOUR_SID sqlplus / as sysdba create directory DUMPS as '/path/to/your/dump/files'; exit; And retry your import. Pending on the user you are using to do the import, ...


1

Simply make these changes: call mysql.insertTables (`Debbie`, `Hopkins`, NULL, NULL, NULL, -- Changed 1, 123456789, 0, NULL, NULL, NULL, 12211985, 12345678, 1) NULL tells the INSERT to invoke the DEFAULTs.


1

The keyword DEFAULT is context sensitive within an INSERT statement only (some systems may support it as well within UPDATE). You can't use DEFAULT within the context of calling a stored procedure. Also, MySQL does not yet support default values for stored procedure parameters either. An alternative can be: delimiter // CREATE PROCEDURE mysql.insertTables ...



Only top voted, non community-wiki answers of a minimum length are eligible