43
votes
Accepted
How to store one-byte integer in PostgreSQL?
No, there is no 1-byte integer in the standard distribution of Postgres. All built-in numeric types of standard Postgres occupy 2 or more bytes.
Extension pguint
But yes, there is the extension pguint,...
- 182k
32
votes
SQL Server has encountered occurences of I/O requests taking longer than 15 seconds
This is far less often a disk issue, and far more often a networking issue. You know, the N in SAN?
If you go to your SAN team and start talking about the disks being slow, they're gonna show you a ...
23
votes
Accepted
What are the current best practices concerning varchar sizing in SQL Server?
Regardless of specific datatype, you need to be able to store whatever the application requests to be stored. You cannot specify something smaller than the max size of what will actually be saved.
...
- 69.5k
18
votes
Accepted
SQL Server has encountered occurences of I/O requests taking longer than 15 seconds
We have a similar setup and recently encountered these messages in the logs. We are using a DELL Compellent SAN. Here are some things to check when receiving these messages that helped us find a ...
- 2,224
17
votes
Accepted
How to reclaim the storage space used by a heap?
If your table doesn't have a clustered index, then deletes don't deallocate empty pages by default.
Your options are:
ALTER TABLE dbo.MyTable REBUILD - which will take your table offline in Standard ...
- 43.2k
13
votes
Drives vs. Mount Points?
It depends on what's at the other end of the mount point. If the mounts are all LUNS spread across the same physical drives, then no gains. If the LUNS are all over a shared, slow, iSCSI channel, ...
- 2,193
13
votes
Accepted
Is there a disk space impact when choosing NULL vs empty array {} for the default of an ARRAY[] column?
AMG's answer worked great but it has some bugs/spelling/case errors. I tried editing it but the edits were rejected. 😕
Here's his recommendation cleaned up (copy/paste-able directly into SQL Editor),...
- 401
12
votes
Accepted
How exactly does the one-byte "char" type work in PostgreSQL?
1. chr(10)
... produces the LINEFEED character (a.k.a. escape sequence \n) and psql displays the character with a newline (indicated by +). Everything correct there.
2. & 3. ascii() produces 128 ...
- 182k
11
votes
Accepted
Attempts to reclaim unused space causes the used space to increase significantly in SQL Server
I'd run DBCC UPDATEUSAGE against the table as a first step, since the symptoms show inconsistent space usage.
DBCC UPDATEUSAGE corrects the rows, used pages, reserved pages, leaf pages and data ...
- 70.5k
11
votes
Accepted
SQL Server - Benefits of splitting databases across different logical drives
is there any performance benefits to keep the data files split across different logical drives?
If all the volumes map to the same set of physical disks on the SAN there's normally no difference.
...
- 47.6k
10
votes
SQL Server has encountered occurences of I/O requests taking longer than 15 seconds
Ok, for anyone interested,
We solved issue in Question couple months ago simply by installing directly attached SSD drives into each of 3 servers,
and moving DB data and log files from SAN to those ...
- 5,964
10
votes
Accepted
What would cause a table with 80,000 records to use 145GB of space?
Create a clustered index so that your table is not a heap. When a row is deleted from a heap, the space will not be reused. Even empty pages don’t get cleared up. This problem won’t happen if your ...
- 16.2k
9
votes
In what cases are BLOB and TEXT stored in-line on InnoDB?
Keep in mind that there are 4 "row formats". A main difference between then has to do with how wide columns are handled.
The reference points to an Answer written early in 2010, a few ...
- 79.4k
9
votes
Accepted
In what cases are BLOB and TEXT stored in-line on InnoDB?
For InnoDB the rule goes like this:
A record can not be fragmented.
At least two records must fit in a page.
If a record size is less than ~7k, all field values are stored in-page.
If the record size ...
- 4,997
8
votes
Drives vs. Mount Points?
In addition to CM_Dayton's answer and Sean Gallardy's answer, one issue not yet identified with Mount Points is related to security. To quote Guidelines for Setting SQL Permissions on Mount Point ...
- 9,487
8
votes
Drives vs. Mount Points?
Based on the results of numerous internet searches I can't find any (post-SQL Server 2000) reason to not use mount points.
The main reason is someone had a bad experience with them (or, conversely, ...
- 35.5k
8
votes
Slow checkpoint and 15 second I/O warnings on flash storage
Last couple of weeks we've been working on getting to the root cause of what could likely be the cause of the occurrence of these I/O issues and slowdown of the checkpoints.
Sounds good. Have you ...
- 35.5k
8
votes
SQL Server has encountered occurences of I/O requests taking longer than 15 seconds
Why storing the data on a SAN? What's the point? All database performance is tied to Disk I/O and you are using 3 servers with only one device for the I/O behind them. That makes no sense... and ...
- 204
8
votes
Accepted
Best practices for SQL Server block size while formatting disks for data and log files
Nothing has changed since that article. You should still use 64KB as your unit size.
That is how the default storage in SQL Server VMs in Azure are configured:
8
votes
Accepted
Why database has char data type when varchar is more efficient?
You should read this article SQL varchar data type deep dive, and specifically the Storage and performance considerations using SQL varchar section:
Because of the fixed field lengths, data is pulled ...
- 39.5k
8
votes
Accepted
Are TOAST rows written for UPDATEs not changing the TOASTable column?
There is a clear answer in the manual:
During an UPDATE operation, values of unchanged fields are normally
preserved as-is; so an UPDATE of a row with out-of-line values
incurs no TOAST costs if none ...
- 182k
8
votes
ReFS Vs NTFS for SQL Server 2019, server 2019
Recently we've been running into errors when snapshotting DBs (operation error 665) One of the solutions of this error is to change the underlying disk type to ReFS.
Yes, NTFS has a limited amount of ...
- 35.5k
7
votes
Seperate volumes for tempdb, data, and backups on SQL Servers in VMs
I like to separate them even if they are pointing to the same drives for disk space reasons. If I run out of space on my backup drive/mount point, it's not going to break the database (not right away ...
- 5,986
6
votes
Accepted
NTFS filesystem fragmentation & other influence vs database files
is there any information about fragmentation of filesystem influence on database/MS SQL Server performance?
AFAIK the only thing we have is about error 665 (NTFS metadata fragmentation) which is more ...
- 35.5k
6
votes
Accepted
Why is my database 12 times bigger than inserted data?
text is the best type for plain text. Text values alone occupy about the same internally (depends on encoding details, too of course: UTF-8?). A small overhead of 1 byte per field in your case. See:
...
- 182k
6
votes
Accepted
How Database Formats Files for Disk
I think it is opportune to start pointing out that, in a relational database —as per Dr. E.F. Codd— information is represented by means of one and only one structure, the relation, which is usually ...
- 8,540
6
votes
Accepted
Calculating total "on row" bytes for each row ... the easy way
How can we access that algorithm directly?
In so far as the answer to this question, specifically, you can't access it directly. There is nothing where you can say SELECT GetMeMaxRowSize(MyTable, ...
- 35.5k
6
votes
Accepted
Understanding the differences in the storage space used by two tables
Based on the information given, one explanation is that the size of the data being stored in the nvarchar(max) column used to be much larger than it is now.
~12 million historical rows averaging 3....
- 29.8k
6
votes
Accepted
varchar(n) size?
It's not that simple.
The n in varchar(n) is just the upper limit of allowed characters (not bytes!). Only the actual string is stored, not padded to the maximum allowed size. That's opposed to the ...
- 182k
6
votes
How to store a table with 1000+ columns, which are mostly null
But a single row can contain more than 1000 criteria
No, you are predicating your data design on a flawed relational model. Putting the cart before the horse. The tail is wagging the dog.
I think you ...
- 339
Only top scored, non community-wiki answers of a minimum length are eligible