35
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,...
- 154k
31
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 ...
- 34.2k
22
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.
...
- 66.1k
19
votes
Accepted
How do completely empty columns in a large table affect performance?
Since the columns e, k, and n can be NULL, I assume "100% empty" means NULL.
NULL storage is cheap. Each NULL "costs" one bit in the null bitmap for storage and otherwise hardly ...
- 154k
19
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,204
18
votes
Best way to populate a new column in a large table?
I don't have a "best" answer, but I have a "least bad" answer that might let you get things done reasonably fast.
My table had 2MM rows and the update performance was chugging when I tried to add a ...
- 281
16
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 ...
- 41.4k
14
votes
Accepted
Storage size of a bigint table
Each row has a minimum of 7 bytes of overhead when stored in FixedVar format (the default). There will also be a (typically relatively small) number of pages used for the upper levels of the clustered ...
- 72.1k
14
votes
Accepted
Impact of CLUSTER on performance
Yes, there can be downsides. If another query looks at a different data segment not determined by the date, it might take a performance hit if rows are spread out over more data pages now. Just the ...
- 154k
13
votes
PostgreSQL data files have size more than data itself
Size can differ due to several reasons:
indexes take some disk space,
there can be several copies of the same record on disk,
slack space in pages.
Indexes take up disk space in order to facilitate ...
- 580
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,086
11
votes
Accepted
What's the difference between Object.bsonsize and avgObjSize returned by db.collection.stats?
My field avgObjSize was around 6442 bytes. I chose a random document in my collection, I typed :
Object.bsonsize(db.collectionName.find({"_id" : ObjectId("5508497c51a990da07b07106")}))
...
- 9,825
11
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 ...
- 154k
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 ...
- 67.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.
...
- 40.6k
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 ...
- 16k
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 ...
- 69.4k
8
votes
Accepted
Should I use ReFS for a new SQL Server?
There is a bit of advice on the internet that suggests for Server 2012 and SQL 2012 I should stick to NTFS e.g. itknowledgeexchange) as database commands like DBCC CHECKDB don't work.
This is no ...
- 61.3k
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, ...
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,102
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
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 ...
- 154k
7
votes
Are Blob Sizes Fixed?
No, as defined, a Blob is "a binary large object that can hold a variable amount of data." It can hold between 0 and 2^16 - 1 bytes (65535). A LONGBLOB can hold up to 4,294,967,295 bytes (or 4GB).
In ...
- 14.2k
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,796
7
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,837
7
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 ...
7
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 ...
- 4,710
7
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:
- 10.9k
6
votes
Slow storage writes: how many milliseconds exactly? SQL Server 2008 R2
You can query sys.dm_io_virtual_file_stats to find out the read and write latency.
You can use the script from here or here - this is snip from my project of designing a comprehensive SQL Server ...
- 61.3k
6
votes
Accepted
Choosing the right storage block size for sql server
The method is the one used in the article that you linked in your question: test it. You can capture a representative workload for your application/database and replay that workload on servers that ...
- 10.9k
Only top scored, non community-wiki answers of a minimum length are eligible