Wikipedia:Reference desk/Archives/Computing/2014 April 12

Welcome to the Wikipedia Computing Reference Desk Archives
The page you are currently viewing is an archive page. While you can leave answers for any questions shown below, please ask new questions on one of the current reference desk pages.

April 12

MSSQL: Determine size of columne/index statistics

I've a table which is heavily increasing it's size, despite I can't figure out why. I compared the row count of the table before I went sleep and it's the same count this morning, however the table size has increased 20 GB (4 Autogrowth steps of 5 GB). Also monitoring the table with SQL Profiler doesn't give me an idea what's going on to this table. My first thought was, that perhaps not the data itself has changed/increased, but some metainformation related to the table. So I tried to find out the size of the table index and found this script here (http://sqlsolace.blogspot.ch/2009/07/tsql-index-size-script.html). The results showed me that the index size is 210 GB which is unbeliveable high since the table size itself is 225 GB. Later I found this command here: exec sp_spaceused @updateusage = N'TRUE'; which also shows the index size. But the result of this command shows a index size of only 70 MB. So I'm not sure who tell me the true, but I prefer the result of the buildin Stored Procedure over the TSQL-Script I found.

However, if it's realy true the index is just 70 MB (not 210 GB) and since the table increses last night 20 GB without increasing in row count, the table must have increased somewhere else. So I'm asking me how big the "Statistics" are. There are 17 statistics, each for every column of the table. But I can't determine how big (physcial store) the statistics are. I searched for a script to determine the statistic sizes, but I din't find some. I found the command 'dbcc show_statistics(tablename, indexname)', but that doesn't show me the physical storage size too. I didn't understand the result at all. But here is one of about 145 rows:

RANGE_HI_KEY					RANGE_ROWS	EQ_ROWS		DISTINCT_RANGE_ROWS	AVG_RANGE_ROWS
B9C37EED-0480-4943-8031-03DC069E8633		966.2053	1		965			1.001614

So my question: Is there any command to determine the physical size of an statistic (not the index itself!)?

(For those who like to advice me just to drop the statistic: I can delete the columne statitics, but not the one which I suppose it's the one how is that big, because it's associated with the Primarykey-Index. Error: 'Cannot DROP the index xxxbecause it is not a statistics collection.'. I also thought about to drop the index itself, but a.) I don't really like to perform this, at least I'm not sure it that matter of my problem b.) I can't as even trying rebuild or disabling the index would give a timeout error. It seams there are some locks and I would have to kill the SPID first. But again: Before I drop the statistic and the index, I really would determine the statistic size first)

--193.47.149.78 (talk) 10:34, 12 April 2014 (UTC)

Do you have multiple indexes defined on the table ? Some newbie DB managers might define an index on every possible search field, to increase search speed, not realizing how much this increases the index and statistics sizes. If there are 3 indexes, this might explain the 70 vs. 210 sizes, where one is an individual index size, and the other is all three. BTW, an individual index size of even 70, when the data size is 225, seems high. I'm guessing that each row is rather short, perhaps around 26 bytes (assuming an 8 byte index per row) ? StuRat (talk) 13:43, 12 April 2014 (UTC)

There is only one index for the primary key, not more. Thanks so far. --77.239.48.100 (talk) 14:18, 12 April 2014 (UTC)

Please describe the data and index (the length and types of each record, not the actual content). StuRat (talk) 13:46, 13 April 2014 (UTC)

Why have a bunch of my Spreadsheet dates become percentages?

4173600.00%, 4171800.00%, 4172300.00%, 4173600.00%, 4173200.00%... for some reason a bunch of the dates in my spreadsheet have turned into percentages like you see here. Note that employees who aren't me input them, so I'm not sure whether they did something wrong with formatting; I'm just the in-office computery guy so I'm expected to fix it. Is there any way to figure out what dates were originally input in the spreadsheet? (Most of these dates are likely from the past few weeks, and are normally input in 4/11/2014 format) 50.43.180.176 (talk) 15:09, 12 April 2014 (UTC)

If you format the cells as dates (as mm/dd/yyyy, or whatever you prefer), they will display as the original dates. For example 4173600.00% (which is equivalent to plain 41736) becomes 7 April 2014. Dates in Excel are stored as the number of days since 1 Jan 1900 (and times as fractions of a day). As for why they changed to percentage format, I would suspect someone (accidentally?) pressed the key combination Ctrl-Shift-%, which (from memory) changes the format of the selected cells to percent, or perhaps clicked something on the Ribbon. Ctrl-shift-# is a shortcut to restore date format. AndrewWTaylor (talk) 15:34, 12 April 2014 (UTC)

Exactly what I needed. Thanks! 50.43.180.176 (talk) 15:48, 12 April 2014 (UTC)

Backing up Windows XP

So I went and bought a copy of Windows 8 for my computer since Windows XP won't be supported anymore. However, I don't want to throw away the Windows XP installation forever; I would like to back up an image of my hard drive onto a flash drive before I install the new OS. Can I just copy everything over, or do I need to use imaging software? And if I do need to use imaging software, where can I obtain such software inexpensively? 71.3.50.250 (talk) 19:54, 12 April 2014 (UTC)

Flash drives cost much more per GB than hard drives, and their data retention is not always the greatest, though hard drives sometimes fail while sitting on the shelf. Normally one backs up flash drives (expensive) to hard drives (cheap) rather than the other way around. I think your best bet is to buy a new flash drive (SSD), install it in your computer and put Windows 8 on it, and just save your old drive (with XP) in a drawer with the contents intact. The SSD will make your computer so much faster you'll wonder how you could ever stand using a hard drive, and you'll have the old drive exactly the way you left it without worrying about imaging vs whatever. 70.36.142.114 (talk) 21:04, 12 April 2014 (UTC)

That was my first thought too, but it ticks me off that I deliberately spent twice as much for a computer with a hard drive and Windows XP installed & ready to go than I would have for the same exact model PC with no hard drive, only to cave to all of the experts' urges for everyone to migrate off of XP (one of the greatest OSes created, btw). If I'm going to buy another HD, I may as well have bought one with no HD in it. I actually have a flash drive big enough to hold everything, but it's one of those off brand things off of eBay that came in from mainland China, so I can't really vouch for its quality. 71.3.50.250 (talk) 03:18, 13 April 2014 (UTC)

I'd suggest either using only Intel or Samsung SSD's. How old is this computer anyway? XP has been discontinued for several years. Upgrading a hard drive to an SSD is well worth the money even if you don't change the software. The speedup makes using the computer a heck of a lot nicer. You almost never see an hourglass icon while watching the disk light flash—stuff just happens with barely perceptible pauses. Most Windows users I know recommend Windows 7 over Windows 8 if that matters. 70.36.142.114 (talk) 04:42, 13 April 2014 (UTC)

FYI, Amazon has some good prices on Samsung SSDs right now. Bubba73 ^{You talkin' to me?} 05:04, 13 April 2014 (UTC)

It's a Dell D430, which I just bought off of eBay (so I won't be going and buying a brand new PC when I just bought one used, won't even consider it). I see a lot of "KingSpec" one's, are those any good? I need to keep costs low because I'm not a millionaire, but if it will really be more efficient for business, I may consider an SSD. 71.3.50.250 (talk) 15:30, 13 April 2014 (UTC)

I don't know anything about Kingspec, but a SSD might cost you as much as you paid for the D430. The D430 came out 6 years ago. Dell 430. It is pretty limited. It might barely meet the requirements for Windows 8, but I don't know if the video card is up to it. I may be wrong, but I don't think it would be a good experience running Windows 8 on it, if it will run. Bubba73 ^{You talkin' to me?} 18:48, 13 April 2014 (UTC)

Yes the GMA945 although it barely met the Aero Glass requirements (hence why the computer is advertised as Vista) was always considered fairly weak. I'm fairly sure it will run though since it was technically enough for Aero Glass on Vista. Since particularly on borderline PCs Windows 8 generally performs better than Windows 7 which generally performs better than Vista and it sounds like drivers exist [1]. I'm presuming the OP is planning to stick to the desktop not Metro apps [2]. Since the OP already owns Windows 8, I guess there's no harm trying particularly since if they want to keep using that computer in an internet connected environment they really should find some alternative to XP be it Windows 8 or some *nix. I do question the wisom of buying an SSD for such a computer though. Nil Einne (talk) 21:30, 13 April 2014 (UTC)

That sounds like you need a PATA drive, so yeah, choices are narrower than with SATA. Kingspec isn't great (I had one, it used the notorious JMicron controller from a while back, and it eventually failed) but it worked while it lasted, so as long as you backup regularly it could work out ok. You could of course replace the hard drive with another hard drive. I do think preserving the old hard drive and migrating to a new one is the best way to 1) be certain you are free of the vagaries of your backup or mirroring software; 2) get your new install onto new media, leaving behind any hard to find computer viruses (though you could end up migrating those to the new drive with your files). There are adapters on ebay (I haven't tried them) that let you put an mSATA SSD into a PATA drive slot. An SSD will really pep up an older computer, but if it's used only occasionally or you're on a super tight budget, then yeah, the expense might be hard to justify. 70.36.142.114 (talk) 23:56, 14 April 2014 (UTC)

If you want to go with your original plan of archiving the drive contents, and you're okay with command-line tools, options include:

• Microsoft's WIM is like SquashFS for Windows: it's an archive format that preserves all NTFS metadata (hardlinks, security attributes, alternate streams...), has decent compression, and can be mounted directly as a filesystem. To create a WIM file you need the imagex command-line tool, and to mount it you need imagex and the WIM filter driver (wimfltr.sys and wimfltr.inf). 7-Zip can extract files from WIM archives. The biggest problem with WIM is that Microsoft doesn't distribute the tools by themselves, only as part of the multi-gigabyte Windows Automated Installation Kit. But here's a way to get imagex.exe without downloading the rest. Also, imagex.exe and wimfltr.sys are signed by Microsoft, so you can trust copies obtained elsewhere as long as you check the signature (which you can do from the Explorer file properties dialog).
• strarc is a tar-like archiver that preserves all NTFS metadata. You can either write the archive file to the backup drive (possibly piping it through xz to reduce the size) or pipe it to a second instance of strarc to do the equivalent of a file-by-file copy.
• You could use dd (probably this one) to make a raw image of the drive. The image can be mounted with ImDisk, and will probably work with more other tools than the WIM or strarc formats will.

I probably can't help with GUI tools, but Comparison of disk cloning software might have something useful.

Dragging all the files to the destination drive in Explorer will probably work too. One problem is that it won't preserve hard links. XP has a number of hard-linked files in Windows\WinSxS. I think XP will still work if the links are broken, but the backup will be larger than you'd expect. -- BenRG (talk) 20:38, 13 April 2014 (UTC)

• If you haven’t broken the seal on Windows 8, I would exchange it for 8.1. Microsoft’s fist releases are always extremely buggy (and probable a lot less secure than the previous incarnation that went before). As you love XP so much, the next thing I would do after creating a clone but before installing 8.1 would be to find someone who has 8 or 8.1 already installed and make sure that it is a viable clone. Run it in Hyper-V to check this. The when you have 8.1 on your own machine, you can still access and run your XP clone virtual – just like it had never gone away. You won't have USB and network access though – which probably is a good thing now that it is unsupported. However if that is too inconvenient, you might be able to access all your old applications via Windows 8, more simply by installing remote desktop in XP once its running in Hyper V on Windows 8. Providing you observe good computer hygiene, got firewalls and aren’t running as admin etc., XP should be a little more secure still (after years of fixes) than Microsoft’s current offering that has loads of new software added. Most home users that have problems, run it as it comes out the box. Antiviral's will still be updated for a while. Also, running in a virtual machine makes it so simply to recover from a nasty getting in – a few clicks of the mouse and you have your uncorrupted cloned image back. --Aspro (talk) 08:22, 14 April 2014 (UTC)

Googling for Preston Gralla I have just found one of his resent articles: Forget the XPocalypse: Sticking with Windows XP can be a smart move . So this is not just me saying it. Therefore, you can take your time to migrate fully over to 8.1. Mainstream Support ends on 8.1 ends at the same time as Windows 8 on January 9, 2018 so most of the bugs in 8.1 will have been dealt with by then. As XP is software and won't wear out like mechanical contrivances you will still have the option of moving your clone on forwards even then -for as long as you wish. However, leave it on a flash and it will die after a year or two. People are always throwing out old computers, do them a favor and take them away and cannibalize for their hard drives. Remember to refresh every 5 years and you have cheap storage. Also, what you are suggesting is not a 'backup' but a copy. A minimum of three copies are a backup. Here is more about backups: [3]--Aspro (talk) 09:01, 14 April 2014 (UTC)

Windows 8.1 is a free update to Windows 8 so it doesn't really matter which one you bought in terms of bugs. I guess it's nominally possible Microsoft may be more generous with Windows 8 licence owners than Windows 8.1 ones in the future but probably not since they haven't been in the past (e.g. upgrading to Windows 8 was the same whether you had XP, Vista or 7, possibly even 2k although I'm not so sure about that). The only thing is installing 8.1 fresh rather than 8 may be a bit more tricky, both because your key may not be accepted for installation and because you may have trouble getting 8.1 media although it sounds like both may be possible probably without doing anything which could violate some EULA or similar [4] [5] [6]. Of course you don't have to install 8.1 fresh but install 8 then upgrading is going to be a bit more annoying and may also result in more space being used (which could be an issue if you're using a small cheap SSD). Nil Einne (talk) 13:15, 14 April 2014 (UTC)

Good points. The other thing I overlooked is that Hyper V only comes with the Pro or Enterprise editions and the OP didn't say which he has. --Aspro (talk) 17:35, 14 April 2014 (UTC)