Database Backups: Things You Need To Do Now

SQL Server 2005, SQL Server 2008, SQL Server 2012, TSQL
I want to say a few things about database backups that you need to know. Wait a minute, haven’t you written about backups before? Why, yes. Yes I have. Aaand… you’re doing it again because? Have you noticed the shocking number of questions that come up on SQL Server Central and #sqlhelp regarding backups? Have you noticed the incredibly huge number of people who don’t have backups at all? That’s why. To get the word out. Oh, good point. Carry on. Because backups are so easy, people tend to discount them. That is, until they need them. Then, suddenly, they become extremely important. Here’s a suggestion: Make databases important now. Learn how SQL Server backup works. Make sure you have backups on your systems. Make sure you have the appropriate…
Read More

Changes to SQL Server 2012 Execution Plans

SQL Server 2012
I’ve been working with execution plans quite a lot in SQL Server 2012. There are a number of changes, most of them associated with new or different functionality. I had not noticed anything really fundamental until recently. I’ve become a huge proponent of always checking the properties of the SELECT statement. There’s so much useful information in there about what’s happened with the plan generation in the optimizer (not low level stuff, but the big picture items) that you should always be checking it first as a fundamental part of your plan examinations. Here’s an example from SQL Server 2008R2: You can see cool stuff like the size of the plan, the time it took to compile, the optimization level, the reason for early termination. Many of the properties on…
Read More

Installing SQL Server 2012 RC0

SQL Server 2012
Very short informational post. If you’re like me and you’ve been running the public Client Technology Preview (CTP3) of SQL Server Denali, uh, I mean 2012, then the news about the release of RC0 is pretty exciting stuff… until you realize that you’re going to have to tear down your virtual test machine, removing the CTP in order to prep for the install of RC0. I love playing with the toys, but I hate working on the toys, if you know what I mean. Great news. You can upgrade from CTP3 to RC0 in place. I know because I’ve done it. When you run the install and you get to the screen that asks what type of install you want, select Upgrade, as highlighted below: I know it doesn’t say…
Read More

SQL Server XQuery Against Execution Plans

SQL Server 2005, SQL Server 2008, SQL Server 2012, SQLServerPedia Syndication, TSQL
One of the greatest things about all the DMOs is how you can combine the information they present to you with execution plans. It allows you to see what a query is doing, even as it’s executing, because the plan is created first. I couldn’t possibly emphasize enough how important that’s going to be in your day-to-day troubleshooting. Even better is the fact that you’re going to be able to make use of XQuery to pull useful information out of the execution plans that are in cache. Or are you? Here’s a query to pull some information out of the procedure cache: SELECT  deps.type_desc, deps.last_execution_time, deps.execution_count, deps.total_logical_reads, dest.encrypted AS EncryptedText, dest.text, deqp.query_plan, deqp.encrypted AS EncryptedPlan FROM    sys.dm_exec_procedure_stats AS deps CROSS APPLY sys.dm_exec_sql_text(deps.sql_handle) AS dest CROSS APPLY sys.dm_exec_query_plan(deps.plan_handle) AS deqp WHERE  …
Read More

Extended Events Data

SQL Server 2012, SQLServerPedia Syndication
I’ve been working quite a bit over the last week or so with extended events in Denali. The sheer magnitude of what you can do with extended events is just becoming clear to me. The interesting thing though is how much the basics are similar to trace. Similar mind you, not the same. For example, the best way to gather trace data is to output it to a file and then read the file into a table for later querying. It’s the same with extended events. There’s even a function that acts as a table: SELECT * FROM sys.fn_xe_file_target_read_file ('C:\APath\Query Performance Tuning*.xel', NULL, NULL, NULL); This can take advantage of roll-over files just like the old function used for traces. You can also provide offsets to read a sub-set of…
Read More

Extended Events and Performance Tuning Knowledge

SQL Server 2008, SQL Server 2012, SQLServerPedia Syndication
I’m working on updating my book, Query Performance Tuning Distilled, so that it reflects the new things available in SQL Server vNext:Denali. I’m going through the first chapters that are all about gathering information about your systems. Performance tuning is all about building up knowledge of how the system is working in order to understand what you need to change in order to improve it. I’m surprised by how much hasn’t changed. But some of the changes are fundamental and huge. Let’s talk huge. Extended Events is huge. Extended Events came out in SQL Server 2008, but very few people, myself included, paid much attention. Those who did found the implementation awkward and confusing. Only a few people persevered enough to discover just how powerful and amazing these things are.…
Read More

Denali & CTP 3

SQL Server 2012
I have Denali installed on a virtual machine on my laptop and I’m working through evaluating it. Actually, I’m working on writing two books using it as the source for the books. But it’s something of a slog, figuring out what’s new, finding out how the new stuff works, seeing if the old stuff broke. Microsoft just released a new guide for doing Denali evals. It’s worth checking out. It might help you short-circuit that discovery process just a little.
Read More

Oh ****!

SQL Server 2005, SQL Server 2008, SQL Server 2012, SQL Server 2014, SQLServerPedia Syndication
Did you ever accidentally close SQL Server Management Studio? And, in closing SSMS, did you get the prompt that says “Save changes to the following items?” And did you, completely unthinkingly, with a query you had just been working on, hit Cancel? Yeah, me neither. What kind of idiot does that…. OK. I confess. I just did that. Silly thing it was, but I had just spent at least 1/2 an hour working on a query and now it was gone…. or was it? I had just run the query and had been looking at the results when I closed SSMS. Initially, I panicked and started thinking about how I could get the data back (somewhere there’s a file I’ve heard). Then it occurred to me, I had just been…
Read More