Hot answers tagged storage
8
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,
...
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 ...
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
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
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
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) = ...
5
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 ...
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
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 ...
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 ...
2
Typically any OLTP database that can't fit into memory has random reads. If you are reading that much from your OLTP database that you need EFDs (what EMC calls SSDs) then you need to first look at putting a LOT more RAM into the database server. It will be a lot cheaper to prevent the reads than to make the reads faster. Those EMC EFDs run about $20k ...
2
Slightly orthogonal answer...
Try and use local disks for tempdb and leave user DBs on the SAN/NAS. This doesn't address the issues you have with the "black box" of the SAN but it does allow you to decouple tempdb from the network. YMMV with clusters.
One thing I've found too is that you have to have high volumes for it to matter too much assuming your SAN ...
2
But notice: it's not 24 bytes (256-bit characters), it appears to be a number only. And a 24-digit decimal number can be stored in just 10 bytes (12 digits, 999999999999, uses 5 bytes, E8D4A50FFF, so squaring that will use 10). A bigint is 8 bytes, and it can go up to 9.2 * 10^18. For all you know, they actually do use bigints and ignore the first 5 digits ...
2
The first thing I generally recommend doing is introducing differential backups to the backup strategy. If the amount of data churn is moderate or low, this can work extremely well for saving space and time because you only back up changes rather than everything. This does add some complexity to restore scenarios, and you must make sure you know what ...
2
SQL Server 2008 R2 SP1 and SQL Server 2012 add a new function, sys.dm_os_volume_stats, which - unlike xp_fixeddrives - is documented and supported (and includes more useful information as well). The catch is that it will only return information for drives that have at least one data or log file on them, since it takes database_id and file_id as input:
...
2
I have my transaction log shipping every 2 days, plus daily backup of the db. My hard disk is 500 GB, but the database size is already reaching 490 GB. I'm afraid it'll run out of diskspace soon.
Taking transaction log backups, as are required for log shipping, internally clears portions of the transaction log, allowing the physical space on disk to be ...
2
I personally have used Raxco PerfectDisk (not associated with the company or any of their employees in any way) to do online defrags of SQL Server LUNs. Works perfectly, if it does slow the server down a bit. I would recommend doing it during periods of lighter activity. When I say "works perfectly" I am referring to it not corrupting the volume or the ...
2
Sql server's query optimizer does not take variations in disk performance into consideration when compiling a query plan. Paul White provides a great overview of Sql Server's cost based optimizer here: http://sqlblog.com/blogs/paul_white/archive/2010/09/01/inside-the-optimizer-plan-costing.aspx
Some key points are:
The optimizer isn't trying to ...
1
The best preventative measure to stop your server running out of disk space is to switch off 'AUTOGROW'. That will prevent your database from grabbing more space, but obviously errors will occur if there is no space left in the file.
One thing to note is that even though your files are using 490MB, you may not actually be using all that space.
Use ...
Only top voted, non community-wiki answers of a minimum length are eligible
