The CIAC video series is a quick and useful way to find out how to do common tasks in a SQL Server environment.

SQL Server Management Studio Keyboard Shortcuts

/in , , , /by

MouseAre you an impatient DBA?  Does it make you crazy to have to mouse around the SQL Server Management Studio dashboard?  Wouldn’t it be nice to know the keyboard shortcuts that can make you go faster?

Back in “the day” we knew that REAL computer people use a keyboard, not a mouse. Mice were for those Apple folk…

And how about SSMS’s split screen — does that ever annoy you?  When you’re working on a big query, and you want to see as much of it as you can, or you want to see as much of the result set as you can fit on a screen, do you know how to make that happen?

And talking about working on big queries, wouldn’t it be nice to see the line numbers in the code? Like, when you were doing procedural programming? Remember those days?

Here ya go…first, the SSMS keyboard shortcuts

http://am2.co/wp-content/uploads/ShortcutsCheatSheet.pdf to download a cheat sheet that I found at http://am2.co/2016/03/shortcuts-cheat-sheet/, Andy Mallon’s blog site. It’s a fun read, and the comments add another handful of keyboard shortcuts — both for SSMS and for Windows.

Next, to avoid the SSMS split screen:

SSMS no split screen

From the top command bar, select Tools > Options

Choose Query Results > SQL Server > Results to Grid, and check “display results in a separate tab”.   If you want to auto-switch to the results tab immediately after executing the query, also check “switch to the results tab after the query executes”.

Click OK to save changes.

NOTE: this will not take effect on any existing query screens that you have open. The next New Query you open, however, will display the new behaviour.

Line Numbers:

Line Numbers

Open SSMS

From the top command bar, select Tools > Options

Choose Text Editor > Transact-SQL > General

Click “line numbers”

Click OK to save changes.

Highlighting:

while we’re at it, are you tired of that blue (or grey) highlighting when in query mode? How about changing the color of highlighted areas to yellow, for instance? Easy ‘nuf!

Highlighting

Open SSMS

From the top command bar, select Tools > Options

Choose Environment > Fonts and Colors

Under “Display Items” highlight “Selected Text”

Use the “Item Background” drop-down list to choose a new color. The sample window is very useful to see what the result will look like before you commit (you can drive someone crazy if you change their selected text background to white or black…but don’t tell them where you found out where to do this!)

Click OK to save changes.

And finally, my favorite…executing a query WITHOUT having to mouse over to the red ! icon, or using the “piano stretch” FN-F5.  ALT-X executes the highlighted query.

There’s no reason why you can’t have fun when working with SSMS. Some of these changes actually make you more productive, too…imagine that! Fun and gets things done — is there anything better?

Let’s stay connected. LIKE me on Facebook. Follow my Tweets. Connect with me on LinkedIn. And make it a great day!

 

 

 

 

 

 

WHAT’S A VLF AND WHY DO I CARE?

/in , , , , /by

Some Background

SQL Server ensures that all changes to the database, either data or structures such as indexes, are preserved by first writing to the transaction log file, than then writing to the physical database.

SQL Server only uses one transaction log per database at a single point in time. It starts writing copies of the changes made at the “front” of the log file, and as changes are committed (written to the physical database) it releases space in the transaction log for reuse. This is called a “round robin” architecture.

SQL Server manages space in the transaction log via VLFs – virtual log files. Inside the one tlog file are a bunch of VLFs – smaller chunks of space that are easier for the SQL Server to manage. Despite the size of a single database, there’s no fixed size for a VLF, and no fixed number of VLFs per physical tlog file, although it’s not uncommon for a tlog file to start life with 4 or 8 VLFs.

SQL Server tlog with VLFs

The space in a VLF is reused when all the transactions/changes stored in that VLF have been written to the physical database following COMMIT records that have been issued for these operations.

What causes the number of VLFs to grow?

Lack of tlog backups and open transactions, mostly.

IF the SQL Recovery Model is set to FULL or BULK-LOGGED and transaction log backups are never taken or are taken too infrequently, then SQL Server never has a chance to clear out the VLFs which have committed changes, so it must continue adding new VLFs to the stack.

IF there is an open transaction, i.e. no COMMIT statement has been issued to “close out” the transaction, then SQL Server has no option but to leave those VLFs which contain portions of the open transaction (“active” VLFs) untouched. And, since it must continue writing copies of other changes that are taking place in the database, it must add new VLFs to the stack.

The only way that SQL Server can add new VLFs to the stack is if auto-grow is enabled. Otherwise, SQL Server will report an “out of space in the log” error and stop processing transactions.

Trivia Fact: when the SQL Server tlog file (auto-)grows between 1-64MB, it adds 4 VLFs to the stack. When it grows between 64-9999MB, it adds 8 VLFs to the stack. When it grows more than 1GB, it adds 16 VLFs to the stack. The default SQL Server auto-grow size is 10%, which means that, as the tlog continues to grow, each set of VLFs created will be a slightly larger size than the previous set.

Trivia Question: if a SQL Server database is created with the default values (tlog file = 10MB), by the time the tlog file has auto-grown to 500MB (1/2 GB), how many VLFs will there be?

Trivia Answer: 200 VLFs

Why is this such a big deal?

Today’s databases are usually large, measuring in GBs (1000 MB = 1 GB) rather than MBs. It’s not unusual to have a 50GB tlog file, and if it’s auto-growing at 10MB per growth segment, the VLF count will be on the order of 20,000.

Too many VLFs impact database operations:

  • Inserts, updates and deletes take longer;
  • Database startup takes longer;
  • Database restores take longer;
  • Database backups are larger because all “active” VLFs (from open transactions) must be backed up;
  • Tlog file fragmentation; SQL Server must search for the appropriate VLF whenever writing to the log file or clearing a VLF for reuse.

 

Is there a problem if VLFs get too big?

Yes. Overly large VLFs (4GB, for instance) can lead to performance problems, also. Clearing a very large VLF takes a long time and takes resources away from transaction processing. Very large VLFs also take more time to be released from the “active” status, since they store so many more changes than a regularly-sized VLF, so this may result in SQL Server having to create even more very large VLFs.

What’s the Solution?

There is no hard and fast rule as to how many VLFs any database should have.

  • Too many VLFs result in tlog fragmentation and the impacts listed above;
  • Too few and very large VLFs cause slow performance.

The goal: have the tlog be as large as necessary, with a reasonable autogrow increment (example: 128MB, or some similar number divisible by 4 or 8 or 16).

How to “Fix” a Tlog File With Lots of VLFs…

  1. Figure out how large the log size should be for the database in question:
    1. How much data is being added to the database in one year’s time?
    2. How often are index rebuilds being done?
    3. Are there excessively-long transactions that are running against the database? Can they be shortened?
    4. Is data being bulk-loaded into the database on a regular basis? How much and when?
    5. What’s the disk usage history? (applies only to mature databases…)
  2. Shrink the tlog to the smallest size possible.
    1. Use the dbcc shrinkfile command to shrink the log file, and do it in increments, within a maintenance window.
  3. Re-grow the tlog file to an appropriate size, again in increments, within the same maintenance window.
    1. Growing too fast can result in too-large VLFs
    2. Use the alter database modify file command to incrementally grow the existing log file
  4. Check the auto-grow settings and adjust to the new database tlog sizing.

 

CODE:

— get the file layout, the amount of space used AND the number of VLFs for each database ————

EXECUTE master.dbo.sp_MSforeachdb ‘USE [?];

exec sp_helpfile; exec sp_spaceused; dbcc loginfo;

 

 

— get a full report on file conditions for a database ————

Select DB_NAME() AS [DatabaseName], Name, physical_name,

Cast(Cast(Round(cast(size as decimal) * 8.0/1024.0,2) as decimal(18,2)) as nvarchar) Size,

Cast(Cast(Round(cast(size as decimal) * 8.0/1024.0,2)

as decimal(18,2)) – Cast(FILEPROPERTY(name, ‘SpaceUsed’) * 8.0/1024.0 as decimal(18,2)) as nvarchar) As FreeSpace

From sys.database_files

 

— check the number of VLFs and force a checkpoint ———-

dbcc loginfo

 

checkpoint

 

 

/*

NOTE: if the database is small enough to be backed up twice during the maintenance window, then switching the recovery model from FULL to SIMPLE back to FULL will work. Otherwise, don’t switch, leave at FULL or BULK-LOGGED, and incrementally shrink the log file, running the dbcc shrinkfile command multiple times.

*/

 

— take full db backup —————————-

BACKUP DATABASE model

TO DISK = N’Z:\SSRSbkups\model\model_full_bkup_20151116_before.BAK’

WITH NOFORMAT, INIT, NAME = N’model Full Database Backup’,

SKIP, NOREWIND, NOUNLOAD, STATS = 25

 

— if the recovery model is not SIMPLE then back up the translog —-

BACKUP LOG model

TO DISK = N’Z:\SSRSbkups\model\model\model_log_bkup.TRN’

WITH NOFORMAT, INIT, NAME = N’model Tlog Backup’,

SKIP, NOREWIND, NOUNLOAD, STATS = 25

 

 

— determine the recovery model —————

select databasepropertyex(‘model’, ‘recovery’)

 

 

— set db to simple recovery model —————————-

ALTER DATABASE model

SET RECOVERY SIMPLE

 

— shrink the log file —————————-

— VLFs are created in sets of 4, 8 or 16 ———

DBCC SHRINKFILE (N’modellog’ , 4)

GO

 

 

— check the number of VLFs

dbcc loginfo                — 8

 

 

— set db back to its normal recovery model —————————-

ALTER DATABASE model

SET RECOVERY FULL

 

 

— check the recovery model —————

select databasepropertyex(‘model’, ‘recovery’)

 

 

Increase the size of the transaction log file:

USE [master]

GO

ALTER DATABASE [AdventureWorks2014] MODIFY FILE (NAME = N’AdventureWorks2014_Log’, SIZE = 20480KB )

GO

 

— take full db backup —————————-

— since the recovery model was switched from FULL to SIMPLE back to FULL,

— it’s highly probable that any transaction log backup taken from this point forward

— would not work with the full backup taken prior to the start of this process…

————————————————————————————–

BACKUP DATABASE model

TO DISK = N’Z:\SSRSbkups\model\model_full_bkup_20151116_after.BAK’

WITH NOFORMAT, INIT, NAME = N’model Full Database Backup’,

SKIP, NOREWIND, NOUNLOAD, STATS = 25

 

— if the recovery model is not SIMPLE then back up the translog —-

BACKUP LOG model

TO DISK = N’Z:\SSRSbkups\model\model\model_log_bkup.TRN’

WITH NOFORMAT, INIT, NAME = N’model Tlog Backup’,

SKIP, NOREWIND, NOUNLOAD, STATS = 25

 

/*  an extra bit of code */

———————————————————

— if you suspect an open transaction, identify it

———————————————————

USE master

GO

SELECT * FROM sysprocesses WHERE open_tran = 1 order by last_batch desc

 

USE Model

GO

DBCC OPENTRAN(‘Model’)

GO

To Learn More About SQL Server Transaction Log Files:

Brent Ozar – Jess Borland video (20min): http://brentozar.com/go/vlf

Thomas LaRock: http://thomaslarock.com/2012/08/the-trouble-with-transaction-logs/

Sebastian Meine: http://sqlity.net/en/1805/eight-reasons-transaction-log-files-keep-growing/

Michelle Gutzeit: http://searchsqlserver.techtarget.com/tip/Testing-transaction-log-autogrowth-behavior-in-SQL-Server

Greg Larsen: https://www.simple-talk.com/sql/database-administration/sql-server-transaction-log-fragmentation-a-primer/

Paul S. Randall: https://technet.microsoft.com/en-us/magazine/2009.02.logging.aspx

Ivan Stankovic: http://www.sqlshack.com/top-10-articles-sql-server-transaction-log/

 

SQL Server Data Compression – Right for You?

/in , , , /by

Data compression is a technology that’s been around since SQL Server 2008. The idea of data compression is that you can selectively choose tables, indexes, or partitions within a database and shrink the amount of space used to store the data.

Now, before you get too excited about this scalability and performance feature, at the time of this writing data compression is limited to the Enterprise Edition of all versions of SQL Server which are currently on the market. The Developer Edition is a single-user SQL Server Enterprise, and it, too, supports data compression.

If you’re still reading, then you’re probably using SQL Server Enterprise, and if you need to squeeze a bit more space out of your disk farm, then you might want to consider data compression.

With the advent of SQL Server 2014, we now speak of data in storage as either “rowstore” or “columnstore”. If you’re managing columnstore tables and indexes then that’s already using columnstore compression, which you can’t configure. The tips and reasoning in this posting reference only rowstore (or regular, as you’ve always know it) data storage.

There are plenty of “how to” posts on SQL Server data compression, but teasing out the “should I or shouldn’t I” is a little trickier. Some of the better blog posts and article have the “should I or shouldn’t I” points interspersed with descriptions of how the technology works. Other posts just show you how to compress data, and don’t really take into consideration the pluses and minuses of data compression.

First, data compression takes resources, lots of resources – CPU and memory, specifically – so if your SQL Server is already CPU-bound or memory-constrained, forget this idea. Go get more memory, faster CPUs, or tune the queries, but don’t add more load to the SQL Server, or your DBA career will be history.

Assuming that you’ve got unused CPU (in the 10-20% range) and available memory capacity, then you’ll want to consider the type of data compression to use – row or page.

Row compression compresses data records, effectively squeezing more records into a single page, so that with each physical I/O you’re reading more records into memory than if the data were uncompressed. That’s highly efficient, since the single most expensive operation in a conventional computer is physical I/O (excluding SSDs and flash drives).

So how does it work?

Glad you asked. Every data row in storage has record headers which contain metadata – information about each field in the record, its length and offset, etc. – this is called row overhead. Sometimes the row or metadata overhead takes more space than the data. Row compression reduces the amount of space required to store the metadata overhead (Microsoft isn’t telling us how it does this), plus it changes the data types in storage. Say, for instance, you have a table with several fixed-length columns, both character and numeric. Row compression will change those data types from fixed to variable-length. Char, integer, decimal, float, datetime, and money data types are all converted to their variable-length equivalents, sometimes resulting in massive space savings. The details for each data type and how they’re converted are covered at https://msdn.microsoft.com/en-us/library/cc280576.aspx.

Page compression enhances row compression by adding prefix compression and then dictionary compression to row compression…so, in order, a page compression operation first compresses the row (row compression, converting fixed-length data types to variable-length and minimizing the metadata overhead); then it determines a valid prefix for data on each page and compresses using that algorithm (prefix compression); then it looks for repeating values on each page, and compresses using that algorithm(dictionary compression).

To get a better idea of how page compression works, a picture is literally worth a thousand words. I’ve borrowed these illustrations from the MSDN library, https://msdn.microsoft.com/en-us/library/cc280464.aspx. Thank you, MSDN.

Data compression pix_1

An uncompressed page of data.

This first figure represents a page of data in storage. Prefix and dictionary compression work page-by-page, determining the best compression “operator” for each page, storing these operators in something called a “CI” structure that is the last entry in each page header.

Notice that the data in this example page contains commonalities – many of the data values start with the same characters (prefix). This might be a common condition with employee ID values, for instance, especially if the table were clustered by these ID values.

Data compression pix_2

A page of data with prefix (step 1 of page) compression applied.

The next figure shows what the data values look like in storage after SQL Server has gone through and done prefix compression. The prefix operators are stored at the end of the page header. The first column, first row, can be decompressed by following the pointer (4) and adding the first four characters of the associated prefix operator to the remaining character in the column, thereby rebuilding the original data value (aaabb). Of course, the pointers are going to be much more complex than this illustration – but you get the idea. The storage savings are pretty phenomenal, close to 25% by my simplistic calculations for this random data set. If you allow a single byte for each character in the data row, and another character for each prefix value in the CI section of the page header, the uncompressed data = 45 bytes, the compressed data = 34 bytes, a 25% reduction.

Data compression pix_3

A page of data after dictionary (step 2 of page) compression has been applied.

Following prefix compression, SQL Server then applies dictionary compression, as it looks for repeating values anywhere on the page and replaces them with a dictionary compression operator and pointers, storing these in the CI structure of the page header.

If your eyes haven’t crossed by now, you’ll notice that the compressed byte count has dropped to 31, or maybe 29, depending on how those zeros in the top row are handled. Nevertheless, the storage reduction is somewhere between 35% and 40%, which could be reason enough to try implementing page data compression.

 

Bottom line: the less randomized the data set, the more consistent the prefixes, and the more common strings in each data value, then the greater the storage savings that you can expect.

When does data get uncompressed?

While the answer to this question doesn’t strictly fit with the reason I’m writing this post, I’m glad you asked, because it shows you’re curious, and that’s good.

Data does not get uncompressed in memory – there is no “clear text” version of the data that some creepy hacker trolling memory could get hold of. Data is compressed on disk and it stays compressed while in memory until one of two things happen: data is read as part of a filter, sort, join or query response, or the data is updated – and then it’s not the entire page that’s uncompressed, just the data fields which are being read or updated.

We’ve talked about how uncompressing data consumes CPU, but those extra CPU cycles may be compensated for by the reduction in both physical and logical I/O. Because more rows of compressed data fit on a single page, more data is read into memory with each physical I/O, resulting in fewer I/Os overall. More data can remain cached in the buffer pool, and smaller volumes of data are written back to the disk. If the data is already in memory, fewer logical I/Os result in less work for the CPUs – possibly compensating for the cost of the CPUs compressing and uncompressing the data. The best savings in physical and logical I/O is during table and index scans. Using execution plans to determine the predominant query resolution operation (scans v. lookups) will help you determine best candidates for compression.

What’s a good candidate?

Now that you understand how data compression works – what’s happening internally, even at a very high level, you can better appreciate the CPU and memory overhead that’s required to decompress the data so that it can be delivered back to the calling applications in some understandable form. So, which situations are best suited for data compression, and which are not?

The ideal data set for data compression – other than having the characteristics I’ve just cited above – is data that is write once, read many. The best candidates are large tables and indexes where the scan-to-update operation is roughly a 3:1 or 4:1 ratio, or higher. If operations on a large table are 75% (or more) scans and 20% (or fewer) updates, then this may be a very good candidate for data compression. Data warehouses, and fact tables, especially, may be excellent candidates for data compression.

This is data compression, so patterns in the data matter. If you have a table with numeric/decimal or fixed-length character data types, where most values don’t need all the space allocated by the data type definition – these columns will row-compress very nicely. Nullable columns where most of the rows are NULL, and columns where the data values repeat (state codes, for instance), are also going to compress well.

What doesn’t compress so well? Filestream data; text and image data types (which, incidentally, are being deprecated with SQL Server 2014) – anything that is stored out of row (data in overflow areas is not compressed); repeating strings within the data value, but without repeating prefixes; data with high variability and little repeatability; variable-length data types.

What can you compress? Anything in the following list that meets the above criteria…

  • A table with no organization (“heap” structure);
  • A table organized as a clustered index;
  • A nonclustered index;
  • An indexed view (remember, these do persist on disk);
  • Partitioned tables and indexes (each partition can be configured differently);

Where are the gotchas?

Data compression is done one time, to whichever data sets (tables, indexes, partitions) on whichever SQL Server instances (production, development, or test) you choose. So what happens if you create a new index on a compressed table – do you think that the new index “inherits” the data compression?

Nope.

That’s right, now you’ve got a table that’s compressed but an index on that table that’s not. It might take you a little while to figure out why the index you just created isn’t giving you the performance boost you were expecting. Well, you say, I can just check with SQL Server Management Studio and to see what’s going on.

Data compression pix_4

Table person.contact has been page compressed but its non-clustered indexes have not – can you tell?

Not easily you can’t.

In the figure to the right, the table Person.Contact is page-compressed; the clustered index PK_Contact_ContactID is also (well, yes, since the data is stored at the leaf level of the clustered index), but the other indexes are not.

It would be great if compressed objects would turn blue or something like the compressed files in Windows Explorer, but that doesn’t happen.

So here’s how you find out what’s compressed, and what’s not. When you execute this code…

SELECT t.name AS [Table], i.name AS [Index], i.type_desc AS IndexType, p.partition_number AS [Partition], p.data_compression_desc AS [Compression]

FROM sys.partitions p

INNER JOIN sys.tables t ON t.object_id = p.object_id

INNER JOIN sys.indexes i ON i.object_id = p.object_id AND i.index_id = p.index_id

ORDER BY t.name, i.type_desc, i.name

…then you get a list of tables, indexes and partitions, and their compression status. It’s much, much easier than clicking through SQL Server Management Studio, checking Storage>Manage Compression for each object.

Back to the issue at hand, and that’s keeping everyone and everything synched. Having your dev and test environments compressed, but your production environment uncompressed is going to produce some interesting and unexpected results in production. The worst case is to have a scattering of objects in some environments compressed, but the same objects in other environments uncompressed. Better to have them all the same.

A recommendation – when embracing data compression, adopt a process something like:

  1. Determine which objects (tables, indexes, partitions) need compression;
  2. Compress in all environments (production, development, test);
  3. Stage the compressions (don’t do them all at once) and compress during low-activity time periods;
  4. Institute a change management process so that objects which are added after initial compression can also be compressed, if needed;
  5. Automate as much of this process so that you’re generating reports periodically, then review those reports to ensure that your systems are in sync.

The next “gotcha” is the case where compressing the data actually causes storage to increase. As you saw at the beginning of this posting, data compression adds extra space to each page header – mostly this is the metadata that decodes the prefix and dictionary portions of page compression. If the data values are too varied, if there’s not a consistency in the first or “prefix” portion of the strings, if there are minimal repeating value strings within the data sets, then it’s possible that this metadata overhead, as it’s called, can be greater than the space saved. In this case, you’ll want to back out of page compression and apply just row compression. If that doesn’t alleviate the problem, uncompress the data completely, and start campaigning for SSD storage devices.

Combining compression

I’ll bet you’re asking yourself if you should bother with compression when you’re doing SQL Server backups now that you’ve got data compression turned on. The short answer is yes.

Backup compression is quite different from data compression; its algorithm is a lot like zipping a file. Data compression and backup compression co-exist nicely, and can result in additional storage space savings, so go ahead and indulge.

There are most certainly some of you who are using SQL Server data compression and you’re considering trading in your spindle-based hard disks for SSDs or flash drive arrays, now that the price of SSDs is dropping while the reliability is skyrocketing. The best advice that I can offer you, based on colleagues who are already doing this, is to keep the data compression in place. Not only are they stuffing each I/O with more records than if the data were uncompressed, they’re now circumventing the I/O bottleneck. And they’re doing the DBA happy dance.

This all sounds pretty cool, but as with all things SQL Server, there are tradeoffs. The greater the compression, the greater the load on CPU (and memory) as data is exchanged with the calling application. If the apps generate a lot of backing and forthing between the client and the SQL Server, with continuous data updating, this could be a very negative feature. If you’re already CPU-bound, if you can’t spare 10-15% CPU capacity, then don’t try this. If you’re already maxed out with memory, address that issue first, and then try data compression. But, if you have lots of unused CPU capacity, memory to spare, and you know that you’re I/O bound, data compression is something that may give your SQL Servers (and you) the relief needed.

Thanks for reading this blog, I hope this has helped answer questions that you might have had about when and when not to use SQL Server data compression. If you’d like to learn more about our products and services, give me a call today at +1 303.526.1839 or click over to www.MountVernonDataSystems.com. If you’re new at the database administration game, check out Coach-in-a-Can® at https://mountvernondatasystems.com/coach-in-a-can.html.

The CoachMichelle Poolet, The Coach

Friday, 13 March, 2015

 

 

 

WHAT’S EATING UP MY HARD DISK?

/in , , , /by

I have a bone to pick with Microsoft. I’m not a Windows Systems Admin, and I don’t think I should have to be in order to use a Windows operating system. I’ll tell my story, and you decide if sysadmin certification is a pre-req to using – in this case – Windows 7.

Lately I’ve had runaway disk consumption on my Windows 7 laptop.  As a SQL Server DBA, I use this little laptop for all my client apps — SQL Server Management Studio, Microsoft Office, personal file storage, etc. It’s not a big system, the local hard disk is is only 278GB.

pink spikey monster-cropWithin the last month I’ve noticed that the free space on this hard disk has been shrinking at an alarming rate, and when the space available “red light” came on, I got really concerned. Granted, I had been saving files to this disk left and right, without considering space requirements — but hey, 278GB can hold a lot of files, right? Apparently not?

Subsequently, I spent many hours moving my files to network storage, and still I was only able to recover less than 30GB of local storage!

WHAT WAS EATING UP MY HARD DISK?

I searched that hard disk for missing space, but I could find nothing. No user folder contained more than a few hundred MBs worth of files.  ARGH!

This is the point in this story when I say that I’m glad I’ve been around computers for as long as I have. Back in the dirty dark days of DOS I had a little utility program that would list folders and their sizes, largest to smallest. Hard disks in those days were measured in a few tens of MB, so file control was critical.

Was there such a utility in today’s market?  OH YES! I found TreeSize (www.jam-software.com/treesize_free/), downloaded, installed, and almost immediately found the culprit — which, incidentally, I could never have done using just Microsoft tools.

Have you heard of this thing called System Volume Information?

If you’re a sysadmin, of course you have! But as a DBA, why would I care? My sysadmin will take care of sysvol stuff, right?

Sure.

SysVol_pix 1

What happens when you try to look into the System Volume Information folder…

I had noticed this sysvol entry in the Windows Explorer tree, and had attempted to check it out, thinking maybe there was a bunch of system stuff that was sucking up the space on disk. Take a look at what you see when you try to check the folder and see what it contains — not a very friendly greeting.

OK, I really didn’t care what it contained at this point, I just wanted to know how large the folder contents were…so, check Properties…

SysVol_pix 2 Zero bytes. This must be one of those folders that Windows uses for temporary files or something.  Except, TreeSize told me that this folder contained about 233 GB worth of files…HUH?

WHY?

Research time!

It didn’t take long to determine what was going on…lots and lots of entries regarding sysvol info, stretching back to the days of Windows XP:

Clean out system volume information folder – Microsoft  https://social.technet.microsoft.com/Forums/en-US/92775152-7330-4da4-8af9-10a04e0bc783/clean-out-system-volume-information-folder

How to reduce the size of the System Volume information (my favorite for concise instructions), http://www.tomshardware.com/answers/id-1813658/reduce-size-system-volume-information-folder.html

How to reduce the size of the System Volume information, http://www.sevenforums.com/performance-maintenance/222588-how-delete-system-volume-information-folder-win-7-a.html

…and even Wikipedia gets into the act…  https://en.wikipedia.org/wiki/System_Restore

BUT — I take exception with this wiki entry. Under Disk Space Consumption, it states:

“The amount of disk space System Restore consumes can be configured. Starting with Windows XP, the disk space allotted is configurable per volume and the data stores are also stored per volume. File[s] are stored using NTFS compression and a Disk Cleanup handler allows deleting all but the most recent Restore Point to free up disk space. System Restore can be disabled completely to regain disk space. It automatically disables itself if the disk free space is too low for it to operate.”

Three points:

1. The contention that files are stored using NT compression — I believe this is not true. You can attempt to apply compression to this folder, but it does nothing — there’s no indication that compression is being done, and it doesn’t even throw an error message. In addition, the display in Windows Explorer is not that of a compressed file.

2. Restore Points are not being managed, and disk space is not being freed up. It’s my understanding that the default config is to allot a maximum of 50% of disk space to sysvol, but in my experience sysvol operations have taken well beyond 50%.  For example, a 278GB hard disk containing 35GB of user data files; when these data files were removed to an external drive, the disk still showed 233GB space used.

3. The contention that “It automatically disables itself if the disk free space is too low for it to operate”  is not true, IMNSHO. My hard disk space was in the red zone, yet GBs were still being consumed — even while user files were being moved off the local hard drive onto the external device.

I’m not sure why this problem surfaced now — perhaps it’s a combination of enough time has elapsed and I’ve been doing more work so that the space consumed by sysvol info has become an issue, who knows? The point is, this is a situation that could trip up a lot of DBAs and ordinary users.  Back in the old days, we could take ownership of the sysvol info folder and administer directly — but starting with Windows 7 that’s no longer possible. Now  you have to jump through appropriate hoops in order to manage the size (see links above for instructions), and how to do this is not obvious. Actually, without TreeSize to tip me off to the sysvol info problem, I would have missed it entirely!

One word of caution: I did reset the sysvol max disk space allocation according to the directions, but when I went back in to check (trust but verify?), the settings had returned to the 50% default. Great.

I can turn off sysvol info entirely — that is an option — but then my system would loose it’s auto-recovery features, and since there have been a couple of auto-recoveries in this laptop’s lifetime, I really don’t want to do that. I just want to use this laptop as it was created…as a shared resource.

So, you tell me, should I or any other non-system admin user need to know about this stuff?  Really?

Thank you for listening to this story. I hope you don’t have this problem with your hard disk, but if you do, I hope this has helped. If you’d like to learn more about our products and services, give me a call today at +1 303.526.1839 or click over to www.MountVernonDataSystems.com. If you’re new at the database administration game, check out Coach-in-a-Can® at https://mountvernondatasystems.com/coach-in-a-can.html

The Coach

 

 

 

Making the Jump from SQL Server 2000 to 2014 – IT AIN’T EASY!

/in , , /by

An Unexpected Journey: SQL Server 2000 to 2014

 

Making the jump from SQL Server 2000 to 2014 — this was an unexpected journey, not for me, but for my client. For nearly 15 years they’d been using SQL Server 2000 as their main production system, and they were happy to do so. Not much changed in their business model, and the database management system was sufficient.

And then their world was rocked. With the obsolescence of the Windows operation system that hosted SQL Server 2000, and knowing that SQL Server 2000 would not function properly on any of the modern versions of the Windows operating systems,

THEY HAD TO DO SOMETHING.

The attached handouts are slides and notes from a presentation that I gave on February 11th, 2015, for the Denver Women in Technology group. I hope you enjoy the preso, and more importantly, I hope you get value from it.

An Unexpected Journey – SQL 2000 to 2014

Tempdb: Checksum or Torn Page Detection or None?

/in , , , , /by

Tempdb performance tuning can be an art unto itself. We probably all know the standard stuff:

SQL Server's Tempdb database

SQL Server has only one tempdb database, which is used for a variety of internal operations.

  • put tempdb on separate disks if you’re still working with direct attached storage — on it’s own spindle if at all possible, and fastest one(s) you have. If you can, put the tempdb data file on one disk and the tempdb log file on a second disk.
  • if you have the capability to determine RAID level for tempdb, RAID 1+0 is best (there’s only one tempdb per SQL Server, so if you loose the tempdb you’re SOL — hardware mirroring is one way of preventing unnecessary downtime). Otherwise, opt for RAID 0 (striping).
  • if your database employs a lot of the objects which are managed in tempdb (temporary tables – local and global, table variables, cursors, sorts inside queries, index rebuilding operations, online indexing, table-valued functions), it’s a good idea to create one tempdb file for every 4 CPUs, up to 8 tempdb data files. Then observe performance and use of the tempdb files, and increase the number of data files if necessary. And, of course, restart the SQL Server so that the new tempdb data file will be recognized and put to use.
  • never configure tempdb with the default initial size and auto-growth as defined in the model database, especially older versions of SQL Server as it came out of the box. Pre-size the tempdb files when you’re installing SQL Server — all the same size for optimal load distribution.

Recently I read a posting about the Page Verify option of database properties, and tempdb not allowing any option other than CHECKSUM, which is not true for SQL Server 2008 R2 and 2012.  This got me to thinking…why would you want to use an option other than CHECKSUM?

TORN PAGE DETECTION is reputedly not as robust as CHECKSUM in terms of data integrity, but might be quicker…is it really?

NONE would be the quickest option if you were focused entirely on fast performance, and really didn’t care about the odd data inaccuracy.

I honestly don’t know — I’ve always gone with safety first and opted for CHECKSUM on all databases, user and system.  Am I missing something by being too conservative?

Have you ever thought about this before? What do you figure would be the conditions under which you’d want to use one property v. another? And obviously, this is NOT something you want to try on ANY production system, not without volumes of testing! Let me know what you think…

To link directly to this post in future:

https://www.mountvernondatasystems.com/blog/tempdb-checksum-or-torn-page-detection-or-none

 

The 833, What Is It?

/in , , , /by

Here’s a story that I’d like to share, about the infamous SQL Server 833. A while ago I was on a performance-tuning call at a client site; they’d been experiencing event 833 from some of their production SQL Servers, and they wanted to know if this was a SQL Server problem or a problem with the disk farm.

To back up just a little, an event 833 (or error 833, as it’s sometimes called) is a signal from the SQL Server that it’s waiting for records that it’s requested. An 833 message reads like “SQL Server has encountered 49 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file […file full path…] in database […database name…]…”.

This message is often interpreted as being a disk I/O issue, and in many cases the 833 can be resolved by optimizing disk operations or just replacing older, slower equipment with the new fast disks. But this one case didn’t resolve quite so easily. You see, the disk farm attached to the SQL Servers in question were composed of flash drives — a flash SAN.

Let’s complicate this situation:

SAN storage serverThe production SQL Servers which were getting the 833s are on the far right of this system diagram. The 833s were coming from different database data files and log files, sometimes during heavy transaction activity and other times during very light transaction activity…so, in other words, the 833s were sporadic and unpredictable.

Given all the “upstream” data stores, processes, and source data implications (no one on staff seemed to really understand how the production system worked), it’s not surprising that there would be some sort of issue in the transaction stream. The stream starts at point-of-sale terminals and data aggregators on the far left and proceeds through load balancers and “soft routers” (this is a distributed environment) and on to error-handling routines before finally arriving at a place where the SQL Servers can begin to do their job.  The good news is that this system is able to handle many millions of transactions a day with only the occasional 833 hiccup. The 833 “wait” always resolves itself within a minute or two, but by then, the upstream congestion is significant.

The client set up a test environment with the intent of replicating the 833 situation to determine root cause. There was only one problem with the test setup…it bore little if any resemblance to production. And, while I was on-site, we were never allowed to use advanced analysis tools on the production systems.

Spoiler Alert! the exact root cause of the 833s was never determined…

So now the challenge to you, dear reader: given what you know so far, what would you surmise might be the cause of the 833s which are being thrown by the SQL Servers?  Remember, that SAN is full of flash drives…

 

Scheduled Job Failed to Run…How to Troubleshoot SQL Server Error Messages

/in , , , /by

Troubleshooting error messages can be a little tricky, especially if you’re new to the task. Here’s a good example of a job that failed without a whole lot of information as to why it failed. This was taken from the Windows Event Log/Application Log:

Log Name:      Application
Source:        SQLISPackage
Date:          7/12/2014 10:45:45 PM
Event ID:      12291
Task Category: None
Level:         Error
Keywords:      Classic
User:
Computer:
Description:   Package “Dynamics DB Integrity Check” failed.

Step 1: this error message is pointing to a job (Application/SQLISPackage) called Dynamics DB Integrity Check.Subplan, so it’s logical to start with the SQL Server Management Studio scheduled jobs. Sure enough, there’s a scheduled job with the same name in the job list, and it’s an active job.

SchedJobErrorMsg_01

 

 

 

 

 

 

 

 

(The jobs with the tiny red downward-pointing arrows as part of the icon have been disabled.)

Step 2: open up the job history for the job and see if there’s any additional information:

SchedJobErrorMsg_02

 

 

 

 

 

 

As it turns out, there isn’t, not in the main entry for the job history description or in the subplan step.

SchedJobErrorMsg_03

 

 

 

 

 

The clues are in the error message Source (SQLISPackage) and Description that starts with “Package …”. The job name is a clue, also – the two-part name usually indicates that this job was created by a Maintenance Plan.

Step 3: Confirm these suspicions by opening the job itself and viewing the content (you’ll have to Edit in order to see the internal configuration of the job, but don’t make any changes – and always cancel out when you’re done, never save *).

SchedJobErrorMsg_04

 

 

 

 

 

 

 

 

 

Maintenance Plans are SQL Server Integration Services packages. When you save a package that you’ve just created, it creates and enables scheduled jobs which run the various tasks that comprise the maintenance package.

Step 4: go to the Maintenance Plans and check the plan named in the job step…but look!  There IS NO MAINTENANCE PLAN!

SchedJobErrorMsg_05

 

 

 

 

 

 

 

 

 

If there were maintenance plans for this SQL Server they would be listed under Legacy>Database Maintenance Plans (this is a SQL 2005 server). Obviously, there are none. Also obviously, at one time there was a maintenance plan.

Reason for the Error Message: the scheduled job is calling a non-existent maintenance plan.

Lesson Learned: when a Maintenance Plan is deleted from a SQL Server, the scheduled jobs that ran the pieces of the Plan are not also deleted. You have to remove (or disable) them separately.

Step 5: remove or disable the scheduled job. For the sake of caution, we’re going to disable the job. Highlight the job name, R-click, choose “Disable”. The result looks like this:

SchedJobErrorMsg_06

 

 

 

 

And those are the steps to troubleshooting an error message about a job failing to run.

* Save v. cancel: when investigating settings or parameters for live jobs or other database properties, it’s always safest to cancel out rather than save when you’re done observing. A “save” could cause changes if you inadvertently modify something in the settings, whereas a “cancel” will simply leave the settings as they were. “Save” when you intend to make changes; “cancel” when you don’t.

 

SQL Backups, Use the Maintenance Wizard or No?

/in , , , /by

Yesterday I was reading an entry in one of the LinkIn forums that I belong to. It went something like this:

“In a simple scheduled backup maintenance plan the Server runs the job but doesn’t create a backup? what could be the reason? In the history of the backup it shows the job ran successfully?”

Obviously, this person had created the SQL Server backup plan using the SQL Maintenance Wizard, which, if you’ve never used it before, walks you through a plethora of maintenance options that you can select from, and then generates an SSIS-like visual plan.  You then schedule the plan to run whenever needed.

The subsequent forum conversation ran the gamut from how to troubleshoot a SQL Maintenance Plan (there really isn’t any good way, without a LOT of additional work, creating error-checking routines in the plan itself), to my philosophy, which was an echo of several individuals who posted before me:

“Take direct control; script out the backups and run them as SQL Agent scheduled jobs. This way, if they fail, you have a record of what happened and why.”

I’ve heard from overworked DBAs that they use the SQL Maintenance Plans because they don’t have the time to write out the code and schedule the jobs to run under the SQL Agent’s control. My experience is that 1) the Maintenance Wizard gives the user too much temptation in selecting options (do you really need to reorganize all the indexes in the database before backing up?), and 2) you don’t have control over what happens and why. With no good troubleshooting tools, when something goes wrong, the time the DBA will spend trying to figure out root cause and fixing it more than compensates for any time saved by using the Maintenance Wizard.

 

How Do You Determine What Operating System & Version of SQL Server You’ve Got Under Management?

/in , , /by

As a new DBA, or if you’re consulting and you need to quickly determine what environments you’re working in, one of the first things you’re going to do is to document (that’s right, you’re going to record this somewhere! it’s one of those “best practices” things…) each server under management.

So how do you do that?

Windows Version: the how-to will vary from opsys to opsys, but essentially, from the start menu, select RUN and type in winver.  For newer versions of the opsys, just type winver into the search box directly above the Start button. The pop-up box shows you just about everything that you might want to know about the operating system except for one very important thing — is this a 32-bit system, or a 64-bit system?

32-Bit or 64-Bit: you’ll have to dig a little deeper, by going to the System report. From the start menu, select RUN and type in system, or type system into the search box just above the Start button. If you have to select your computer by name, you’ll see an option to View System Information — that’s what you want. Hint: if it doesn’t say x64 or 64 Processor or something like that, you’re dealing with a 32-bit system. Hint-hint: if you’re on a 64-bit machine, then the SQL Server instance(s) will also be 64-bit…the opsys and SQL Server have to match.

SQL Server, how to tell which version you’re dealing with: Open Management Studio (I’m going to pretend that everyone is now on SQL Server 2005, although I, myself, support one instance of 2000), connect to a SQL Server instance, open a query window, and type in one of these two queries:

select @@version  — the old standby, and yes, it’ll work on SQL Server 2000, also. The output looks something like this:

How Do You Determine What Operating System & Version of SQL Server You've Got Under Management?

Or, you can run this query:
 SELECT SERVERPROPERTY(‘productversion’) AS ProductVersion,
          SERVERPROPERTY (‘productlevel’) AS ProductLevel,   
         SERVERPROPERTY (‘edition’) As ProductEdition

How Do You Determine What Operating System & Version of SQL Server You've Got Under Management?

— by specifying various server properties and giving the output column names, you can produce something that looks more like a real report.

And there you have it!  Yes, there’s lots more to find out about your servers under management, but this is a place to start.