Tag Info

Hot answers tagged

17

That's because of Instant File Initialization. In short, SQL Server can take advantage of this privilege for database data files (not transaction log files). What this means is that SQL Server does not have to zero out the data file(s) when initializing. Without the "Perform volume maintenance tasks" privilege granted to the SQL Service account, upon the ...


5

There are two types of activity to consider here: reads and writes. As you correctly point out, MDF/NDF writes are done through the checkpoint process, and users shouldn't have their transaction time affected. Reads happen when the data that is needed is not yet in RAM (in the buffer cache). So ideally, user activity shouldn't be affected often. But when ...


3

You have a time series (measurements) organized by id (clustered index). I am yet to see a single case where using id as clustered key for time series makes sense. All queries will ask for date ranges. Organize by time: CREATE TABLE measurements ( id bigint IDENTITY, parameter_id int NOT NULL, measuretime datetime NOT NULL, value float NOT ...


3

As Ypercube said in his comment you seem to have an uncorrelated subquery. This means that for each way you are trying to build a line containing 1,000,000 odd nodes. Also it it would be best to put a Geography constructor around the result of the subquery. The tutorial's update statement that you referenced has got a correlated subquery, because the ...


3

You would need an index on each column in order to avoid a sort of every row and column during the ranking process. That would of course introduce significant overhead as scores are updated continuously. Probably not an option unless you have a high-end hardware configuration. The ranking processes could be offloaded onto a read-only copy maintained via ...


2

It's a gradual process and the effect is comparatively small. But of course, looking up entries in system tables gets slower with lots of rows. Those are just regular tables. Highly optimized, but regular tables. There are indexes to keep the effect small. I have never actually noticed an effect in my biggest database with a couple of hundred tables and ...


2

You have a odd system. Your database size is 350+ G and you have 32 bit system I would say this is a system which I would never like to have in my environment. Its very difficult to manage 350 G database on 32 bit SQL Server which has VAS limit(by default) of 2 G. You are bound to face memory pressure going ahead. AWE in 32 bit system only allows SQL ...


2

The whole point of using DATE as a type is so the database can efficiently query the data. It's the same reason you store a number as an INT and not a VARCHAR - so the engine can make intelligent decisions. If you use the LIKE operator on a date, you lose the benefits of having chosen the correct data type. Using MONTH(birthday) allows MySQL to grab the ...


1

It depends a little on what storage system you have behind the scenes. You see, read and write IO operations are very different. On a RAID 5 to perform a single block write you must: Read the update block. Read the parity block. Write the new block. Write the new parity block. So for a single random write, RAID 5 needs 4 operations per write. This is ...


1

(More of a bunch of comments, plus a redundant answer.) Your two examples are not identical -- one is limited to 2015; the other is not. WHERE birthday BETWEEN '2015-04-01' AND '2015-04-01' + INTERVAL 1 MONTH would be able to use INDEX(birthday), but that only covers those who will be born next month. Even if you had a mnth TINYINT UNSIGNED COMMENT ...


1

I go for SELECT * FROM customers WHERE MONTH(birthday) = 4; since its the common way to select by month


1

(Not an answer, but some pitfalls that make it difficult to design this for efficiency.) More than half the users will have only one link. Some users will each have over 100K links. More than half the sites will have only one page. Some sites will each have over 100K pages. What does it mean? It means that any form of indexing, compression, etc, needs ...


1

In almost every use case, InnoDB is preferred over MyISAM. So, yes. To make sure the indexes, etc are converted correctly, see if anything in MySQL to InnoDB checklist needs to be addressed. Note that key_buffer_size should be decreased and innodb_buffer_pool_size increased. In MyISAM, an UPDATE blocks all other operations on the table. In InnoDB, it ...


1

I think the optimiser is right. When you use INCLUDE, it only stores the included column values on the leaf level of the index, they do not make up the key. So what it is suggesting is that it can decide which branches of the index to scan (measuretime is the key, so it leaves a huge chunk of records out), which means the WHERE doesn't need to test each row. ...


1

Since the selective predicate on the big table eav_value_text_data big table is v.value = 'rs145368920', you need an index on value more than anything else. The index on attribute is hardly relevant - only in combination with the first to allow index-only scans if possible: CREATE INDEX eav_value_text_data_val_att_idx ON eav_value_text_data (value, ...


1

Like @dezso commented, creating a new table and dropping the old used to be faster in old versions, but not any more with the new implementation in pg 9.1. The most common problem with CLUSTER is that it requires an exclusive lock on the table, which does not go well with concurrent access to it. The solution to this problem is pg_repack, which does not ...


1

All columns on both tables are indexed. Enough said. That is not the right way to go about indexing. WHERE db2.tb2.c1 = db1.tb1.c1 db2.tb2 needs INDEX(c1) -- Keep in mind that a PRIMARY KEY is a UNIQUE key is an INDEX, so do not redundantly add INDEX if you already have PRIMARY KEY(c1). and c6='X' and c9 <='y' and c10>= 'Z' and c12>='N' ...


1

This is caused by Nagle's algorithm. CouchDB sends the HTTP headers and the response body in separate calls, causing the kernel to not deliver the response body for 40ms. As a workaround, you can use this LD_PRELOAD shim to completely turn off Nagle's algorithm for the database process.


1

You should not enable and disable MAXDOP setting on the server using a scheduled job. You should set it away from the default of 0 to some thing sensible for your environment. This script will help you to get a good start. Alternatively, you can use OPTION (MAXDOP N) as a query option. Microsoft provides a good starting point in this KB-2806535 article ...


1

This link is probably a good place to start. Setting the MAXDOP at the server level depends on a lot of things like whether you're using a virtual or physical server, how many physical cores you have, hyperthreading being enabled or not and the number of NUMA nodes you have. But you if you tinker with that, you should be made aware of increasing your cost ...


1

Before start, please note that external keys are usually bad as primary keys, because they are larger than needed, they can change and some people may not have one, or have duplicated ones (even if legally that shouldn't happen). Speaking correctly, the EIN is a code of 9 digits. As such, the technically correct value should be a string. In particular, for ...



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