Hot answers tagged

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,...
user avatar
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 ...
user avatar
  • 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. ...
user avatar
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 ...
user avatar
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 ...
user avatar
  • 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 ...
user avatar
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 ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
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 ...
user avatar
  • 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, ...
user avatar
  • 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")})) ...
user avatar
  • 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 ...
user avatar
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 ...
user avatar
  • 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. ...
user avatar
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 ...
user avatar
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 ...
user avatar
  • 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 ...
user avatar
  • 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, ...
user avatar
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 ...
user avatar
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 ...
user avatar
  • 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 ...
user avatar
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 ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
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 ...
user avatar
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:
user avatar
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 ...
user avatar
  • 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 ...
user avatar
  • 10.9k

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