Tag Info

Hot answers tagged

12

Absolutely possible, because someone with the resources to store 6021188640340442162025691220451771208370039202613309330051794368412 Terabyte definitely has enough money to get a custom made database system for his purposes as well. I might be available as a contractor, for only 0.01€ per Terabyte.


10

You need to rebuild the clustered index after making the columns sparse. The dropped columns still exist in the data page until you do this as can be seen with a query against sys.system_internals_partition_columns or using DBCC PAGE SET NOCOUNT ON; CREATE TABLE Thing ( ThingId int IDENTITY CONSTRAINT PK PRIMARY KEY, USER_CHAR1 nvarchar(150) null, ...


8

You have to use compression (columnstores should do it). I suggest RLE (Run Length Encoding), you can store (5HpHagT65TZzG1PH3CSu63k8DbpvD8s5ip4nEB3kEsreAbuatmU ,1MsHWS1BnwMc3tLE8G35UXsS58fKipzB7a ,1Q1pE5vPGEEMqRcVRMbtBK842Y6Pzo6nK9) as the value and 115792089237316195423570985008687907853269984665640564039457584007913129639936 as the run length. Document ...


7

Like most general guidelines, it is a an oversimplification in its most positive light. At best, it is a good starting point (provided you don't aren't keeping the 1:1 core:data file ratio with a large amount of cores). There is no replacement for proper design and proper follow-up monitoring and baselining. The reason behind having multiple data files ...


7

1/4 to 1/2 files to cores has long been the recommendation... But there's now even better guidance. At PASS in 2011, my good friend Bob Ward, who’s the top guy in SQL Product Support, espoused a new formula: if you have less than 8 cores, use #files = #cores. If you have more than 8 cores, use 8 files and if you’re seeing in-memory contention, add ...


7

Your problem is not disk fragmentation. Your problem is RAM and application table scans: 4GB RAM ... 68GB ... Page life expectancy 20 seconds You need way more RAM. As in your new server should have way, way, way, way more than 12GB. Start with 64 GB, it costs basically dimes. And yes, fix your app to use indexes. 20 seconds is very clear indication of ...


7

SQL Server 2008 R2 installed on a virtual server IMO, the SQL Server VM should live with the rest of the VMs. If this device goes kaput (the Cybernetics website says nothing about the number of controllers in this model range, so presumably there's only one), you don't want all your eggs in one basket. Hopefully there are other, better protected devices ...


6

If eventual consistency is acceptable and all your queries are aggregates then perhaps a low-latency OLAP system might work for you. Your requirement sounds a bit like an algorithmic trading platform. This type of architecture is often used in trading floor systems that have a requirement to carry out aggregate statistical analysis computations on up to ...


6

tl;dr Yes you can do a rough and ready calculation but benchmarking is advisable if you need accuracy. There are many factors beyond raw disk performance that can influence IOPs, in particular the RAID controller. If you have the technical specifications for the disk, the quick calculation to determine IOPs is: 1 / (average latency + average seek time) = ...


6

You didn't mention the Database platform, I can give insight on SQL Server. SQL Server ensures durability with a concept called WAL. Write ahead logging. This means that all changes are first written to log before the are applied tot the data files. When a row needs to be altered. The corresponding data (and possibly index) pages get fetched from disk into ...


6

Unfortunately there are only 1080 atoms in the visible universe. But with only one atom given, it could be possible to describe the data purely by its position in the universe. The database has 2266 records with 52 bytes each, that is a database size of 52⋅2266 bytes. That means there are 25652⋅2266 ≈ 101082 possibe states the database could adopt. Using ...


5

All one has to do is run these queries: MyISAM SELECT CONCAT(ROUND(KBS/POWER(1024, IF(PowerOf1024<0,0,IF(PowerOf1024>3,0,PowerOf1024)))+0.4999), SUBSTR(' KMG',IF(PowerOf1024<0,0, IF(PowerOf1024>3,0,PowerOf1024))+1,1)) recommended_key_buffer_size FROM (SELECT LEAST(POWER(2,32),KBS1) KBS FROM (SELECT SUM(index_length) KBS1 FROM ...


5

No, backup / restore will keep all the fragmentation. Probably better to add a filegroup with files in the new location, and recreate all of your user tables on the new filegroup (by recreating the indexes with DROP_EXISTING, and as an online operation if possible). You won't be able to eliminate the original files entirely, but if you've moved all the user ...


5

One of the main benefits of utilizing multiple files and filegroups is that you have great control over file growth. Also, prominently you can control and optimize I/O performance, as putting database files on separate physical disks can lead to faster I/O. If you have an I/O expensive query across two tables, putting then on different disks can lead to ...


5

I am not entirely sure how much this information helps, but the system table pg_stats contains a correlation column: select schemaname,tablename,attname,correlation from pg_stats where schemaname='public' order by correlation; From the manual Statistical correlation between physical row ordering and logical ordering of the column values. This ...


5

To answer your question literally, yes, in MySQL, views do exist as occupied space on the disk. But of course they do: if the didn't, where would they exist? If you rebooted your server, how would the views persist? I imagine what you really meant was "do MySQL views occupy physical space in proportion to the number of rows they contain?", in which case the ...


5

I would go for the same database, for the reasons you state in your question. If you're worried about performance or growth, create a new "logging" filegroup and put the logging table(s) on that. If things grow out of hand in the future, you can move the .ndf (the physical file that contains the new filegroup) to a different physical drive. Even if you ...


5

Before jumping at one article you should do a little research, after a short Google search "windows storage pools vs raid" I found this article which goes more in-depth. As far as I can tell this new tech doesn't completely replace raid but could be considered an alternative to raid ONLY if you are using windows server 2012. It also may not make sense if you ...


4

But, from reading into it more, separate physical disks for mdf and ldf files don't really apply when it comes to SSD's. Correct? The original reason for splitting log and data files off onto seperate disks was 2 fold - latency and bandwidth on the drives. SSDs don't remove these restrictions, but they do decrease/increase the limits quite ...


4

Random thoughts The statistics etc weren't disable on rebuild into new filegroup? Maintenance tasks do not look at the filegroup? Heavily fragmented disk? Try MAXDOP 1 Exactly same fillfactor etc? The IO looks way off so I wonder if stats or fragmentation is causing an issue


4

I'm guessing you're actually running on a clustered instance. EXEC master..xp_fixeddrives is returning all the drives that the underlying OS is aware of, so a lot aren't available to your cluster instance use: SELECT * FROM sys.dm_io_cluster_shared_drives instead, which will just list the ones available to your instance


4

I profiled the query and this is the portion that returns the 'Disk Usage by Top Tables' results: exec sp_executesql @stmt=N'begin try SELECT TOP 1000 (row_number() over(order by (a1.reserved + ISNULL(a4.reserved,0)) desc))%2 as l1, a3.name AS [schemaname], a2.name AS [tablename], a1.rows as ...


4

SQL Server doesn't have any access to anything that low level. Typically Windows can't even see the RAID group information given that each hardware vendor stores that differently and typically exposing that information to the OS doesn't have any benefit. You'll need to work with your hardware team to make the drive labels match, but even then that isn't ...


4

If you have a single RAID10 volume then as far as SQL Server is concerned you have one volume and you can't control how things are stored, splitting things into extra files unnecessarily will likely have detrimental effects as it would on a single disk. If you wish to try gain performance benefits from segregating data between spindles then you need to ...


4

Your question reminds me of PostgreSQL. It has a feature called TOAST (The Outside Attribute Storage Technique). PostgreSQL features TOAST tables in the event the length of the row data is too small. I have discussed TOAST before in the DBA StackExchange May 01, 2012 : what is bigger than a longblob? Mar 21, 2012 : Are many NULL columns harmful in mysql ...


4

Improving on Remus' answer, you could use a more powerful compression, storing nothing in the database. How would that work? Just like the difference between a method and a generator in Python, the difference between a function that returns a value or yields it when needed. Lets call it "lazy evaluation". As an example, assuming the table will hold all ...


4

As in the original article you linked: The question here is this: if you’re not segregating the workload at the physical resource level, is there any need to segregate the workloads at the logical level? For example, if tempdb and my user databases are in a single pool of disk on the array, should I bother having them on multiple LUNs (Logical Disks) on ...


3

You may believe that this is a different question but my answer is going to be essentially identical assuming the additional information from the prior question is the same-- you're using automatic extent allocation and you're not sure how Toad is determining these numbers. The data you're getting from Toad appears to be incorrect or, at least, misleading. ...


3

The data you're getting from Toad appears to be incorrect or, at least, misleading. If you are using a locally managed tablespace with automatic extent allocation, Oracle will determine your initial and next extent sizes automatically. In 11.2, the first 16 extents are going to be 64k in size (for a total of 1 MB). The next 63 extents are going to be 1 MB ...


3

Passport numbers should definitely be encrypted in the database. You should investigate the legality of collecting and storing this information before proceeding.



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