A Fine Slice Of SQL Server
Erik Darling wants to know if fill factor affects index fragmentation:
It’s not just queries that reading extra pages can slow down. DBCC CHECKDB, backups, and index and statistics maintenance all have to deal with all those pages. Lowering fill factor without good reason puts you in the same boat as index fragmentation does, except regular maintenance won’t “fix” the problem.
Like everything else, the appropriate fill factor depends upon your context.
Comments closedJames Serra explains why you might want to use a data lake:
To refresh, a data lake is a landing zone, usually in Hadoop, for disparate sources of data in their native format. Data is not structured or governed on its way into the data lake. This eliminates the upfront costs of data ingestion, especially transformation. Once data is in the lake, the data is available to everyone. You don’t need a priority understanding of how data is related when it is ingested, rather, it relies on the end-user to define those relationships as they consume it. Data governorship happens on the way out instead of on the way in. This makes a data lake very efficient in processing huge volumes of data. Another benefit is the data lake allows for data exploration and discovery, to find out if data is useful or to create a one-time report.
I’m still working on a “data swamp” metaphor, in which people toss their used mattresses and we expect to get something valuable if only we dredge a little more. Nevertheless, read James’s article; data lakes are going to move from novel to normal over the next few years.
Comments closedKenneth Fisher on varchar versus nvarchar:
In any study of Data Types in SQL Server you are going to have to look at the various string data types. One important component is the difference between nChar vs Char and nVarChar vs VarChar. Most people reading this are probably thinking “Well that’s ridiculously easy.” If you are one of them then I want you to read these two facts about these data types.
Char and VarChar nChar and nVarChar Stores ASCII UNICODE Size Always one byte per character. Always two bytes per character. One of these is incorrect. Do you know which one?
The correct answer is “both are wrong.” Then you get into debates about what a “character” is, how certain languages (like Hebrew and Arabic) have layers of modifiers which modify semantic context, etc. etc. Strings are probably even harder than dates.
Comments closedKendra Little has a list of three things a junior DBA should know:
Confession: I was a Junior DBA for a long time before I had a clue about this. It’s not unusual– many DBAs pick up existing databases and it’s natural to accept that the settings are correct.
Except, usually they aren’t. Usually, the last person who set them up just kinda guessed.
Guess what? You’re responsible for whatever they guessed.
Kendra’s three items are definitely junior-level, but we all start somewhere.
Comments closedMicrosoft is now recommending diskspd. At this point, I suppose SQLIO could be considered “venerable” but tip a 40 on the curb for a good tool.
Comments closedNiko Neugebauer has two new posts up on columnstore index changes with SQL Server 2016.
First, row group merging with clustered columnstore indexes:
Row Group merging & cleanup is a very long waited improvement that came out in SQL Server 2016. Once Microsoft has announced this functionality, everyone who has worked with SQL Server 2014 & Clustered Columnstore Indexes has rejoiced – one of the major problems with logical fragmentation because of the deleted data is solved! Amazing!
Just as a reminder – logical fragmentation is the process when we mark obsolete data in the Deleted Bitmap (in Columnstore Indexes there is no direct data removal from the compressed Segments with Delete command and Update command uses Deleted Bitmap as well marking old versions of rows as deleted).
Second, Stretch DB with columnstore:
Stretch DB or alternatively Stretch Database is a way of spreading your table between SQL Server (on-premises, VM in Azure) and a Azure SQLDatabase. This means that the dat of the table will shared between the SQL Server and the Azure SQLDatabase giving the opportunity to lower the total cost of the local storage, since Azure SQLDatabase is cheap relatively expensive storage typically used on the local SQL Server installations.
This mean that the table data will be separated intoHot Data & Cold Data, where Hot Data is the type of data that is frequently accessed and it extremely important (this is typically some OLTP data) and the Cold Data (this is typically rarely or almost never accessed archival or log data).
For the final user the experience should be the same as before – should he ask for some data that is not on the SQL Server, then it will be read from Azure SQLDatabase by the invocation of remote query, joined with the local results (if any) and then presented to the user.
These two posts are must-reads if you work with columnstore indexes.
Comments closedAndy Leonard has a long and useful post on collecting ETL metrics in SQL Server 2016:
“In an age of the SSIS Catalog, why would one ever employ this kind of metadata collection, Andy?” That’s a fair question. The SSIS Catalog is an awesome data integration execution, logging, and externalization engine. There are a handful of use cases, though, where enterprises may opt to continue to execute SSIS packages from the file system or the MSDB database. Perhaps the biggest reason to do so is that’s the way the enterprise is currently executing SSIS. When SSDT-BI converts pre-Catalog-era (2005, 2008, 2008 R2) SSIS packages to current, it imports these packages in a “Package Deployment Model” SSIS Project. This allows developers to upgrade the version of their SSIS project to SSIS 2016 (and enjoy many benefits for so doing) while continuing to execute SSIS packages in the file system. Kudos to the Microsoft SSIS Development Team for this backwards compatibility!
Andy asks the question I wanted to ask and gives a good answer.
Comments closedDenny Cherry alerts us to a free preview of a new Microsoft Press book:
The best thing about this book, is that it is free, there isn’t even a soul sucking registration to go through. Just click and download. It’s available is a standard 8.5×11″ PDF (standard US Pages) as well as a smaller PDF for mobile. If you are looking for EPUB and MOBI files you’ll need to wait a few more weeks as they are supposed to be available starting in January (don’t hold me to that, I’m just going off the MSDN post.
Denny has a chapter on SQL Server security improvements that looks particularly interesting to me.
Comments closedBill Anton has more information on performance monitoring for Analysis Services:
For query workloads, we can see important information about every single query that hits the system including details such as the total duration of the query, query text (MDX/DAX), start and end times, as well as the associated user account. We can also determine details as to how the query was executed such as the number of partitions scanned, aggregation hits/misses, cache hits/misses, other queries running at the same time, etc…all of which have an effect on the performance of any one particular query. A secondary benefit is that we’ll be able to identify the usage pattern(s) of folks using the cube. For example, is usage low/moderate throughout the week with a heavy spike on Friday mornings?
Bonus note: it looks like there will be an xEvents for Analysis Services GUI in SQL Server 2016.
Comments closedSteph Locke has more on documentation auto-deployment (and the original Curated SQL entry):
So I went through and converted everything in my Rtraining to this and realised it messed up my slide decks – it’s been so long since I had built a pure knitr solution that I forgot that
rmarkdown::render!=knitr::knit. For my slidedecks, if I wanted the ioslides_presentation format, I needed to usermarkdown::render. The problem with that has been the relative references to the CSS and the logo.To solve this I read about the custom render formats capability and created afunction that produces an ioslides_presentation but with my CSS preloaded by default. This now means that I can produce slides with better file referencing.
Steph has put up all of her R-related presentations and documentation as well, so check that out.
Comments closed