Tag Info

Hot answers tagged

10

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.


9

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, ...


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

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 ...


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

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 ...


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

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

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

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 ...


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 ...


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

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

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

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

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 ...


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

What I would do is to benchmark the performance before and after the changes. There should be a performance gain after tempdb is moved to another drive. Use DMVs like sys.dm_io_virtual_file_stats to see the read and write wait times for the DB files. Use the perfmon physical disk counters: Avg. Disk sec/Read, Avg. Disk sec/Write, Disk Reads/sec, Disk ...


3

One thing to consider is that the log files are sequential writes where as the data files are non sequential. That is one of the reasons for separate LUNs. Log files write faster if they are on their own LUN because the spindles don't have to skip around, just write sequential. If you add in a data file then the spindles have to skip around and you lose ...


3

I am not entirely sure how much this information helps, but the system table pg_stats contains a correlation column. From the manual Statistical correlation between physical row ordering and logical ordering of the column values. This ranges from -1 to +1. When the value is near -1 or +1, an index scan on the column will be estimated to be cheaper ...


3

I have created a very simple demo of how partition switching might work for you: USE tempdb GO SET NOCOUNT ON GO IF OBJECT_ID('dbo.largeTable') IS NOT NULL DROP TABLE dbo.largeTable IF OBJECT_ID('dbo.largeTable1') IS NOT NULL DROP TABLE dbo.largeTable1 IF EXISTS ( SELECT * FROM sys.partition_schemes WHERE name = 'ps_date' ) DROP PARTITION SCHEME ps_date ...


3

Perfectly ok - especially since you can add and subtract integer to / from date directly in Postgres. However, int2 might not be better than a plain integer. True, integer occupies 4 bytes instead of 2, but many operations are optimized for integer. Among other things, integer is the default numeric type for numbers without decimal point. For int2 you ...


3

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 ...


2

What you can do is restrict access to the SQL server and the Windows server that SQL is on. There are database server level roles with sysadmin giving you the highest privileges. Review this link to see what you will need to give your temp. The temp should never be in the sysadmin server level role, based on what I read from your requirements. Then there ...



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