Questions tagged [storage]
For questions about the persistent storage of database data.
300
questions
15
votes
1
answer
19k
views
What are the current best practices concerning varchar sizing in SQL Server?
I'm trying to understand the best way to decide how big varchar columns should be, both from storage and performance perspectives.
Performance
From my research, it seems that varchar(max) should ...
- 181
7
votes
2
answers
4k
views
In what cases are BLOB and TEXT stored in-line on InnoDB?
I've googled this and can't find it, you can see Rick James mention it here.
... BLOB and TEXT are not always stored separately
The answer on What is the difference between MySQL VARCHAR and TEXT ...
- 59.6k
0
votes
1
answer
47
views
Shrinking the database partition if autogrow is turned off?
I want to know:
Are there chances of shrinking the database partition if autogrow is turned off?
Also, are there chances of data fragmentation and increase in seek times if we fully extend size ...
- 103
-3
votes
2
answers
94
views
What are some real world applications that generate large numbers of records in a database every day and what are those metrics? [closed]
I'm trying to get some perspective in today's day and age what is considered a large amount of data or large number of records generated in a day and what real world applications are doing that. I ...
- 30.5k
2
votes
2
answers
293
views
How does the optimizer in Oracle Database use I/O statistics?
I would like to help Oracle's query optimizer make better-informed decisions. I have been unable to find much information on how the optimizer determines the cost of I/O operations, other than ...
- 21
16
votes
1
answer
1k
views
CREATE DATABASE on RAW partitions no longer works?
I'm attempting to create a database using two raw, i.e. unformatted, partitions.
Microsoft Docs states you can do this, you simply need to specify only the drive letter of the raw partition, as in:
...
- 68.5k
3
votes
1
answer
291
views
Tempdb size strategy
I've recently started my role as a DBA and my initial task has been to take ownership and redeploy a database provided by a 3rd party to help us conform to GDPR legislation.
As a consequence, I've ...
- 343
5
votes
4
answers
4k
views
Best way to store physical stock data in a SQL db
I work for a retail company and we are in the process of building a new data warehouse. One element we need to store is physical stock data as to how many units of any sku are available at a certain ...
- 143
1
vote
1
answer
95
views
How can I find the stored binary value of a DOUBLE value in MySQL?
Given the following table, backed by MySQL 5.7/InnoDB:
CREATE TABLE mytable(mydouble DOUBLE) SELECT 0.005 `mydouble`;
how can I find what exactly InnoDB is storing on the disk for the value 0.005?
...
- 327
0
votes
1
answer
112
views
Is it possible to allocate data free for certain table?
After deleted a large dataset (~50GB) and optimized tables, some tables have data free (~50GB) and parts of tables have only data free (~KB). As my understanding, the data free are reused and not ...
- 143
1
vote
1
answer
381
views
Post Migration error in sql server error log
I am seeing below error messages when we have moved/migrated databases to new server/instances from SQL2K12 stanadlone to cluster
last target outstanding: 20800, avgWriteLatency 30
average throughput:...
- 2,149
0
votes
1
answer
94
views
Storage provisioning for Microsoft SQL Server
I need help with my Microsft SQL Server file setup!
I have a TEST Microsoft SQL Server with 3 RAID1 (SSDs):
C: (240 GB) - O/S installed
L: (460 GB)
M: (900 GB)
I have a PROD Microssoft SQL Server ...
- 13
0
votes
0
answers
42
views
Storing a lot of text / string data strategies - analysis and solution
I have a LOB application backed by SQL Server 2016.
Over the past few weeks, theres been issues whereby the SQL Server start throwing timeout errors - which has led to a bit more digging into the ...
- 121
2
votes
1
answer
169
views
Durability of storage in second generation google cloud sql
How durable should I expect data for a Google Cloud SQL instance to be? Just a single instance, with the high-availability option disabled.
AWS explicitly mention that Amazon Aurora data is stored in ...
1
vote
1
answer
174
views
Optimizing column types for reduced overall database size
Given the following table:
CREATE TABLE avl_historico (
fecha timestamp with time zone NOT NULL,
latitud double precision DEFAULT 0 NOT NULL,
longitud double precision DEFAULT 0 NOT NULL,
...
- 929
3
votes
1
answer
2k
views
why SSD is slower than HDD SAS 15K on DBMS PostgreSQL? [closed]
Someone help me analyze the two execution plans below (Explain ANALYZE used), is the query 9 of TPC-H benchmark.
I'm using two servers HP Intel Xeon 2.8GHz/4-core - Memory 8GB. O.S. Debian8, using ...
- 201
1
vote
0
answers
71
views
EMC Recoverpoint works for database?
As a solution for replication, to protect VM, LUNs and recovery including database, EMC Recoverpoint was presented by our vendor.
But in EMC paper's, the only one database related example ilustrates ...
- 421
2
votes
3
answers
2k
views
How to create a normalized database to manage inventory for a small business? [closed]
I am writing a Java application to manage the inventory of a small furniture business. I know little about databases, I have a bit of experience with SQL queries and more experience with Java ...
1
vote
3
answers
333
views
SQL Server 2016 SP 1 on HP 3PAR environment causes weird head blocker
My client is on HP 3PAR hardware. Three servers are on different geographical locations, connected with a 40 gbit fibre connection. The SAN is SSD only, the machines have lots of cores.
The ETL ...
3
votes
1
answer
2k
views
Why is my database 12 times bigger than inserted data?
I have a short question concerning my database size. I need to insert data in a database. Before the insert, some calculations need to be done.
The point is: from 50 mb plain data (~700,000 lines), ...
- 45
1
vote
1
answer
115
views
What does column family-oriented datastore mean in practice
I was reading this article about HBase. Within it, the storage format of HBase is described:
HBase is referred to as a column family-oriented data store. It’s also row-oriented: each row is indexed ...
- 111
2
votes
1
answer
237
views
NTFS filesystem fragmentation & other influence vs database files
As probably everybody knows, NTFS has a flaw in design which causes fragmentation during filesystem usage. On the other side, MSSQL uses files as database storage method, and what is more, typical ...
- 243
1
vote
0
answers
752
views
SQL TempDB PCI-E NVMe card - 4K or 64K
Our primary OLTP workload is tempdb heavy (both reads & writes) based on virtual_file_stats DMV, and based on SPs all of which heavily use #tmp tables.
The server has 400GB of memory, more than ...
- 1,116
1
vote
0
answers
823
views
Why is SQL Server unable to access partitions stored on UNC paths?
I have a database with its primary file and log file stored locally. One table is split into 101 partitions which are stored on a Windows share on another server accessed by a UNC path. Each partition ...
- 1,114
1
vote
1
answer
284
views
Is SQL Server's fill strategy the same for multi-file tempdb as multi-file (non-tempdb) filegroups?
Will tempdb I/O from a single query be split across multiple tempdb files? (assuming that tempdb is configured to use multiple files, of course!)
For non-tempdb databases, MDSN seems to say that yes, ...
- 751
1
vote
1
answer
550
views
What does "volume" mean in MAX_IOPS_PER_VOLUME?
When setting MAX_IOPS_PER_VOLUME in a Resource Pool, what exactly does "volume" mean? Specifically, how many "volumes" would be the following cases:
Locally attached disk that's split into two ...
- 751
2
votes
1
answer
113
views
How does combining columns save disk space?
I am creating a table in SQL server and inserting records. Three columns from this table are AAA int, AAB int and AAC int. If I merge AAB and AAC into one column Comb1 char(2), it consumes 20% less ...
- 131
1
vote
2
answers
1k
views
SQL Server Disk throughput slower than diskspd
This is a bit of a broad question but I am trying to understand a storage performance behavior with two of our servers.
I was following https://www.brentozar.com/archive/2015/09/getting-started-with-...
- 1,728
6
votes
2
answers
2k
views
How to reclaim the storage space used by a heap?
I have a table without a clustered index in SQL Server 2008R2 Standard with a lot of unused space, as shown in the following image:
How do I reclaim unused space from MyTable?
Said table is declared ...
- 71
8
votes
1
answer
209
views
Can't SQL Server store NULLs in fixed-length columns?
I came across this statement in the official Oracle documentation:
In Microsoft SQL Server, only columns with variable-length data types
can store NULL values. When you create a column that ...
- 234
3
votes
3
answers
600
views
Configuring physical storage for a Microsoft SQL Server installation
I'm configuring a Microsoft SQL Server installation which will use a SAN for storage. For SAN storage I have:
A small number of SSD disks
A larger number of 10K disks
My SAN supports storage ...
- 191
3
votes
1
answer
3k
views
Storing PLC Data in SQL Server database
I'm developing a software solution that requires storing raw data points originating from many PLCs (programmable logic controller, used to control industrial machines such as conveyor belts and ...
- 33
0
votes
1
answer
854
views
What is an efficient storage method to store Graph Data from GraphX (Spark)?
I generate graph data using Spark's GraphX library and I need an efficient way to store graph data. I have access to Apache Cassandra and ideally I want to store the graph data in there but the issue ...
- 101
1
vote
0
answers
67
views
What parameters should I use to measure storage I/O capabilities
I want to measure the throughput of my disk subsystem using: https://gallery.technet.microsoft.com/DiskSpd-a-robust-storage-6cd2f223
what parameters should I use to closely emulate SQL I/O?
Is there ...
- 181
1
vote
0
answers
278
views
Why ndb data memory is 94% if i m using disk storage cluster
I have following structure
2 data node
1 management node
2 sql node
i have create one log file of initial size 200M
then created table space and added 3 data file
data1.dat (initial size: 10GB)
...
2
votes
1
answer
2k
views
How do I increase the storage available for a SQL Server instance on AWS RDS?
The Amazon docs state that you cannot increase the storage allocated to a SQL Server RDS instance:
Note that you cannot change the storage capacity nor the type of storage for a SQL Server DB ...
- 5,887
1
vote
1
answer
400
views
How calculate per tenant disk usage in a multitenant database with tenant_id columns in postgresql
I am using Django and postgres for a multi-tenant webapp. We have around 100 tables and each table has a tenant_id column, identifying the tenant of that particular row. I need a solution that would ...
- 143
0
votes
1
answer
363
views
How PostgresSQL stores duplicated VARCHAR values in a column
I have a VARCHAR(255) column in a table with about 50 million rows. In that column there is only 10 unique values, which act as labels. I'm wondering whether Postgres does something smart and stores ...
- 103
1
vote
1
answer
408
views
NVMe+bcache as huge storage cache for vertica database
We are in process of selecting hardware for 3 machines vertica cluster capable to serve 20-30TB of data (15TB compressed). This translates to 8TB of post-RAID storage. With 16 CPU cores, the storage ...
- 121
0
votes
1
answer
43
views
Searching for scalable storage solution
In one project I save statuses of processing in MySQL table:
`id` `name` `email` `has_apples` `has_bananas`
1 Bob a@b.c 1 0
2 Mike c@d.e 0 1
3 ...
- 121
0
votes
2
answers
48
views
Storing meta about a cropped photo [closed]
Want to know how to save cropped images in the database? The info about the images or reference to the cropped images in itself?
Working on a marketplace for artists where artists can upload their ...
2
votes
2
answers
258
views
What is the most efficient way to store a bunch of empty fields, and ones with only a few choices?
When I say efficient, I mean total storage(kb), and not excessively burdening the CPU.
I have 70 columns of data. The last 35 fields have always been NULL.
The table has only 4 columns of ...
- 141
0
votes
2
answers
159
views
How to install MongoDB database as a service in ESxi?
I want to know is it possible to install mongodb directly to the ESXi without guest OS?
Please tell me how to setup Data storage as a service environment inside ESXi.
- 101
2
votes
1
answer
694
views
Can we use VMDK for FCI on SQL Servers?
Can I share a VMDK among 2 nodes for an active passive setup for a SQL Server Failover Cluster setup?
Most of the articles I see mention to use RDM's, however we use Veeam backup for the environment ...
- 625
1
vote
1
answer
1k
views
Allocation Size for Backup disk on SQL Server
What would be the optimal allocation size for a backup disk for SQL Server? We have a 2014 instance which was using Veeam backups till recently, but we decided to switch to local backups again for ...
- 625
0
votes
1
answer
3k
views
move AG database files between disks
Environment: SQL 2016 SP1, AG with 2 replicas, sync commit, no readable secondary. 1 file in the PRIMARY FG, 1 for the index FG.
Problem: How to move the files for a database in this AG, from the ...
- 976
2
votes
0
answers
110
views
RethinkDB storage calculations
I am trying to estimate storage requirements for the data our application will store. After doing some research I can pull out data from the stats table. However, I don't understand how those numbers ...
- 23
9
votes
2
answers
3k
views
How exactly does the one-byte "char" type work in PostgreSQL?
I often see people talking about "char". I've never used it. It's defined in the docs as,
The type "char" (note the quotes) is different from char(1) in that it only uses one byte of storage. It is ...
- 59.6k
1
vote
1
answer
146
views
Shared logarch across multipe db2 HADR instances
Background
We are looking into building a multiple standby hadr cluster. The Auxiliary standby is in a geographically separate location.
from Developer works
In a multiple standby system, the ...
- 11
13
votes
4
answers
11k
views
Drives vs. Mount Points?
The previous Senior DBA set up mount points for all of our drives across every SQL Server throughout the company. The new Senior DBA is horrified by mount points wants to change our standard (mainly, ...
- 811