Blog

Corrupt Your Database – On Purpose! [Video]

Corruption: it can strike at any time. You know this, so you have your page verification option set to CHECKSUM and you run DBCC CHECKDB regularly. When the dreaded day finally arrives, what do you do? If you haven’t faced corruption yet, or you want to brush up your repair skills, Jes will show you how to corrupt your database with a sample database and a hex editor – and how to fix it!

Tune in here to watch our webcast video for this week! To join our weekly webcast for live Q&A, make sure to watch the video by 12:00 PM EST on Tuesday. Not only do we answer your questions, we also give away a prize at 12:25 PM EST – don’t miss it!

 

The script is available for download, with the understanding that this is purely for test purposes, and will never, ever be used on a production database. Ever.

How to Restore a Page in SQL Server Standard and Enterprise Edition

One of the many restore features in SQL Server is the ability to restore one or more pages of data. This can be very convenient in some narrow situations – for example, corruption occurs on one page or an oops update is made to one record.

The page restore process is not straightforward, however, and, as I recently discovered, the Books Online article about it is confusing. See, you have to perform the restore offline in all versions except Enterprise Edition – but the only example Books Online gives is…Enterprise Edition.

Here’s a straightforward breakdown of how to do a page-level restore both offline and online. For the sake of brevity, let’s say I have two databases – TestRestoreOnline and TestRestoreOffline. Both are in Full recovery. Each has one damaged page, which I’m going to restore. (For a full demo script, click here.)

Offline

You should already have an existing full backup – mine is at D:\SQL Backups\TestRestoreOffline-Backup1.bak. I also have one transaction log backup, D:\SQL Backups\TestRestoreOffline-LogBackup1.trn.

/* This example uses an OFFLINE restore, which is applicable to all versions of SQL Server. */
USE master;
GO

/* The NORECOVERY statement in this last log backup makes the database "offline" - you don't actually set it OFFLINE. */
BACKUP LOG TestRestoreOffline TO DISK=N'D:\SQL Backups\TestRestoreOffline-LogBackup2.trn'
WITH NORECOVERY;
GO

/* Restore full backup, specifying one PAGE.
I used sys.dm_db_database_page_allocations to find the page number. */
RESTORE DATABASE TestRestoreOffline
PAGE='1:293' --Have multiple? Separate with commmas.
FROM DISK=N'D:\SQL Backups\TestRestoreOffline-Backup1.bak'
WITH NORECOVERY;

/* Restore log backups */
RESTORE LOG TestRestoreOffline FROM DISK=N'D:\SQL Backups\TestRestoreOffline-LogBackup1.trn'
WITH NORECOVERY;

RESTORE LOG TestRestoreOffline FROM DISK=N'D:\SQL Backups\TestRestoreOffline-LogBackup2.trn'
WITH NORECOVERY;

/* Bring database "online" */
RESTORE DATABASE TestRestoreOffline
WITH RECOVERY; 

That is an offline page restore. By putting the database in a NORECOVERY mode before the restores begin, the database can’t be accessed.

Online

An online page restore is only available in Enterprise Edition. This will allow users to access other objects in the database while you are restoring the page(s) needed.

You should already have an existing full backup (D:\SQL Backups\TestRestoreOnline-Backup1.bak) and log backup(s) (D:\SQL Backups\TestRestoreOnline-LogBackup1.trn).

/* This is an example of an online page restore. */
USE master;
GO

/* Restore full backup, specifying one PAGE.
I used sys.dm_db_database_page_allocations to find the page number. */
RESTORE DATABASE TestRestoreOnline
PAGE='1:293' --Have multiple? Separate with commmas.
FROM DISK=N'D:\SQL Backups\TestRestoreOnline-Backup1.bak' 
WITH NORECOVERY;

/* Restore log backups */
RESTORE LOG TestRestoreOnline FROM DISK=N'D:\SQL Backups\TestRestoreOnline-LogBackup1.trn'
WITH NORECOVERY;

/* With Enterprise Edition, the "online" restore - a log backup with NORECOVERY - goes here. */
BACKUP LOG TestRestoreOnline TO DISK=N'D:\SQL Backups\TestRestoreOnline-LogBackup2.trn'
WITH NORECOVERY;
GO

/* Restore the last log backup */
RESTORE LOG TestRestoreOnline FROM DISK=N'D:\SQL Backups\TestRestoreOnline-LogBackup2.trn'
WITH NORECOVERY;

/* Restore database */
RESTORE DATABASE TestRestoreOnline
WITH RECOVERY; 

The steps for an online restore differ slightly. The tail-log backup is taken after all the other log backups are applied, instead of at the beginning of the sequence.

Want to know more?

Backup and recovery skills are essential for every DBA, but it can be complicated! If you want to learn more, check out my Backup & Recovery Basics or Backup & Recovery Advanced video training!

Are SQL Server Functions Dragging Your Query Down?

In most coding languages, functions are often-used blocks of code that can be reused from multiple locations, leading to less code – and cleaner code. SQL Server also lets us create functions that can be used the same way. They are reusable blocks of code that can be called from multiple locations. So, if you need to format phone numbers a certain way, or parse for specific characters, you can do so using a function.

The question is, how much work is SQL Server doing when you call a function? If it’s the SELECT clause, is it called once – processing all rows – or once for each row in the result set, regardless if that’s 1 row or 100,00? What if it’s in the WHERE clause?

I’ll let you in on a little secret: if a function is used in the SELECT or WHERE, the function can be called many, many times. If the function is very resource-intensive, it could be causing your query to be very slow – and you would never see the execution of the function within the execution plan of the calling query.

Yep, SQL Server’s execution plans can be a bit vague when it comes to functions – and by “a bit vague”, I mean, “They don’t show up at all”. You need to dig deeper!

I’m going to run a few demos against the AdventureWorks2012 sample database in a SQL Server 2014 instance to show this!

First, I create a scalar-value function that will return the five left-most letters of a LastName.

CREATE FUNCTION [dbo].[ParseLastName](@LastName VARCHAR(50))
RETURNS VARCHAR(5)
AS
-- Returns the 5 left characters of the last name
BEGIN
DECLARE @ret VARCHAR(5);

SET @ret =
LEFT(@LastName, 5)

RETURN @ret
END; 

Then, I create an Extended Events session to track statement completion. (Note: I have only tested this on SQL Server 2014, no lower versions.) (Using SQL Server 2008 R2 or earlier? You could create a server-side trace to capture sp_statement_completed and sql_statement_completed, but it won’t give you some functionality I’ll show later.)

CREATE EVENT SESSION [CaptureFunctionExecutions] ON SERVER
ADD EVENT sqlserver.sp_statement_completed(
ACTION(sqlserver.sql_text,sqlserver.tsql_stack)),
ADD EVENT sqlserver.sql_statement_completed(
ACTION(sqlserver.sql_text,sqlserver.tsql_stack))
ADD TARGET package0.ring_buffer
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=OFF)

GO 

I start the Extended Events session, and then turn on actual execution plans.

I start with a simple query, which returns 19,972 rows.

SELECT LastName
FROM Person.Person;

The execution plan shows an index scan and has a cost of 0.10451.

function 1

Looking at the details of the index scan, I see Estimated Number of Executions is 1, and Number of Executions is 1.

Let’s look at the same query when it performs the same calculation as the function – LEFT(LastName, 5).

SELECT LastName, LEFT(LastName, 5)
FROM Person.Person; 

There’s now an additional operator – a compute scalar. The cost has risen slightly to 0.106508.

function 2

Now, I will modify the query to call the function from the SELECT clause.

SELECT LastName, dbo.ParseLastName(LastName)
FROM Person.Person; 

Looking at the execution plan, I see an index scan and a compute scalar. The cost is the same as before –  0.106508.

function 3

Expanding the properties for the compute scalar, I see the function, but it says there is only one execution.

function 4

A quick glance at my Extended Events live feed tells a different story.

function 5

If I add grouping by statement, I can see the function was actually executed 19,972 times – once for each row in the result set.

function 6

That’s a lot more work than advertised!

Does the same thing happen if the function is in the WHERE clause?

SELECT FirstName, LastName
FROM Person.Person
WHERE dbo.ParseLastName(LastName) = 'McCar'; 

Two rows are returned. The execution plan now has an index scan, a compute scalar, and a filter. The cost is 0.118091.

function 7

The Extended Events session again shows 19,972 executions – once for each row in the index.

function 8

The data isn’t filtered out until after the function is called, so it is executed once for each row.

Conclusion

These examples prove that whether one or many rows are returned as the query result set, if a function is used in the SELECT or WHERE, the function can be called many, many times. It could be one of the top resource-consuming queries in your server!

How can you see if a function is bringing your server’s performance down? Look at the top queries in your plan cache using our sp_BlitzCache tool, by total CPU and by number of executions, to see if this is happening to you.

Email Query Results Using a SQL Server Agent Job

SQL Server Agent is one of my favorite tools – it allows you to automatically schedule jobs, alert you if things are going badly, and capture information into database tables.

One common request is to execute a query and send the results via email on a regular basis. This task is a great match for SQL Server Agent and sp_send_dbmail. Setting up a job and a step that uses sp_send_dbmail with the @query parameter, and building on it with other available parameters, you can easily accomplish this.

Let’s take a look at a job I’ve created. The first step is to gather metrics from the sys.dm_os_sys_memory DMV. I insert the data into a table in my master database.

Job Step 1

Job Step 1

The second step in this job uses sp_send_dbmail to send the results of the query from today to a database mail profile account.

Job Step 2

Job Step 2

Let’s look at the command more closely.

EXEC msdb.dbo.sp_send_dbmail 
	@profile_name = 'Main DB Mail profile', 
	@recipients = 'jes@brentozar.com', 
	@subject = 'Memory Values', 
	@query = N'SELECT total_physical_memory_kb, available_physical_memory_kb, system_memory_state_desc, collection_date_time
			FROM  MemoryDMVHistory 
			WHERE CAST(collection_date_time AS Date) = CAST(GETDATE() AS DATE)  
			ORDER BY collection_date_time DESC;', 
	@attach_query_result_as_file = 1, 
	@query_attachment_filename = 'Memory Values.txt' 
  • @profile_name is the Database Mail profile you want to use to send the email. You must have at least one profile already set up.
  • @recipients is the list of email addresses to send the email to. If you have more than one, separate them with a semi-colon.
  • @subject is the email subject.
  • @query is the query you wish to run. MSDN says it “can contain any valid Transact-SQL statements”, but I haven’t tested the limits of this statement yet.
  • @attach_query_results_as_file has two options: 0 or 1. When set to 0, the query results are included in the body of the email. When set to 1, the results are included as an attachment.
  • @query_attachment_filename is optional, and is the name and file extension of the attachment. I recommend setting this, with a .txt or .csv extension.

There are other options you can add as well, such as @execute_query_database, @query_result_header, @query_result_width, and more.

When I execute the job, I receive this email:

agent job query email

When I open the .txt attachment, I will see my query results:
agent job query text file

This isn’t well-formatted, so I may want to modify the results with @query_result_header and @query_result_width.

Both SQL Server Agent jobs and sp_send_dbmail give you a lot of flexibility for creating and emailing query results. Learn to use them effectively!

Get in the (SQL Server) Community Spirit [Video]

Thanks for tuning in to our webcast again this week! To join our weekly webcast for live Q&A, make sure to watch the video by 12:00 PM EST on Tuesday, September 9! Not only do we answer your questions, we also give away a prize at 12:25 PM – don’t miss it!

The SQL Server community is unlike any other technical community. We have so many helpful people and our pick of resources. When you want to contribute, you have many options – from speaking to writing, attending conferences to running user groups, answering forum questions to writing articles. Which should you invest your time in to help your career and personal growth? Let me share my experiences with all of these so you can make the best choice.

Have questions? Feel free to leave a comment so we can discuss it on Tuesday!

SQL Server Transparent Data Encryption (TDE) Common Questions

Transparent Data Encryption is a way to encrypt your data in SQL Server. It affects the data and log files of the database. I recently gave a webcast on this topic, and got some great questions about TDE.

Lock that data up tight!

Lock that data up tight!

What versions of SQL Server is it available in?
It is in 2008+. (Let me ask again…why are you still using SQL Server 2005?)

Is TDE only available in Enterprise Edition?
Yes, this is an expensive-edition-only feature.

Can TDE encrypt at the table or column level?
No, it’s all or nothing.

Does TDE provide encryption of backups?
When you enable TDE on a database, the backups will, by nature of the feature, be encrypted. However, it’s not possible to encrypt only the backup files and not the data. Native backup-only encryption is available as of SQL Server 2014 (Enterprise, BI, and Standard editions).

When I turn TDE on, will all the data pages need to be read? Will this take some time?
Yes, and yes. Test it in your development environment first to determine how long it will take to perform this in production.

Does TDE work with…
Failover clustering? Yes – there is only one copy of shared data.
AlwaysOn Availability Groups? Yes, but you have to set it all up with T-SQL.
Database mirroring? Yes – the data will be encrypted on both instances.
Log shipping? Yes – the data is encrypted on both instances.
Replication? Technically…but it’s a pain. The certificate must be installed on all subscribers, and the data is not encrypted as it is distributed.

What other questions do you have?

The Mysteries of Missing Indexes [Video]

SQL Server is trying to help you – when you run a query, you see a missing index request. Before you run that CREATE INDEX script in production, consider a few things. How helpful will the index be? Is it similar to an existing index? Why is it recommending three indexes that are very similar? Jes will solve some common missing index mysteries in this 30-minute webcast.

Is Transparent Data Encryption Right for Your Data? [Video]

Transparent Data Encryption is a method to encrypt your SQL Server data on disk. Using it can present challenges, though. How does it work? How does it interact with other SQL Server features? What does it not encrypt? Join Jes as she explains the ins and outs of TDE.

Want to try it yourself (in a test environment!)? Download the script.

Comparing Estimated and Actual Execution Plans in SQL Server

Let’s say you want to have an addition built on your house. You contact a contractor, who comes to your house, looks at your lot, looks at what exists, and asks you questions about exactly what you want. He then gives you an estimate of the work – approximately how long he thinks it will take his crew, what supplies will be needed, and the cost of those supplies.

SQL Server gives you a similar option. When you write a query, before committing to it, you can request an estimated execution plan.

The estimated execution plan is designed to show what SQL Server would most likely do if it were to execute the query. Using statistics, it estimates how many rows may be returned from each table. It chooses the operators it will use to retrieve the data – scans or seeks. It decides how to best join the tables together – nested loops, merge joins, hash joins. It is a reasonably accurate guide to what SQL Server will do.

You can view an estimated execution plan for an individual statement, a stored procedure, or a function. The plan can be text, XML, or graphical. To view text, use SET SHOWPLAN_TEXT ON. For an XML version, use the command SET SHOWPLAN_XML ON. To view a graphical plan in SSMS, click the Display Estimated Execution Plan button in SSMS.

Est Exec Plan

There are some cases in which SQL Server can’t create an estimated execution plan. If your query has parameters in it and values aren’t passed in, SQL Server can’t interpret those – it needs literal values. If the query references a temp table that is not declared, the plan also can’t be generated.

Now, let’s go back to our construction project. We’ve signed the contract and the contractor begins work. While the addition is being framed, you decide you want to add an extra room, or add windows, or make the ceilings higher. The contractor has to adjust for this in terms of time and cost. This will change how long the project takes, and how expensive it is.

Executing a query in SQL Server is no different. The actual execution plan is shown after a query is executed. The difference here is that SQL Server can tell you exactly how many reads were performed, how many rows were read, and what joins were performed.

If it’s a long-running query, it will take a while to get the execution plan. Parameters, if required, must be passed in.

The text plan is generated using SET STATISTICS PROFILE ON. The XML version of the actual plan can be viewed by using SET STATISTICS XML ON. A graphical version can be generated in SSMS by using the Include Actual Execution Plan button.

Actual Exec PLan

There are some cases in which things that show up in the estimated plan will not show in the actual plan. For example, when you call a scalar-value function, the estimated plan will show it – the actual plan will not. (This is why the impact of functions can be very misunderstood.)

“IF I VIEW AN ESTIMATED PLAN, THEN IMMEDIATELY RUN THE QUERY AND VIEW THE ACTUAL EXECUTION PLAN, WHY DO I SEE DIFFERENCES?”

The query optimizer is going to use statistics on the tables and indexes to decide how to perform the actual query execution. If statistics change for any reason between the time you estimate a plan and when the query is actually run, you can see differences.

Changes to table schema, indexes, or even the data can affect the statistics. If a new index is added, or rows are updated in the table, when the query optimizer executes the query, it could choose a different set of operators than it did during estimation. Sometimes the differences between the estimated and actual plans can be large!

How can you prevent this from being a problem? Make sure statistics are updated on your tables and indexes. Auto update stats will automatically refresh statistics if a specific number of rows in a table change – after a table reaches 500 rows, roughly 20% of the rows need to change. (Exact details about that are here http://support.microsoft.com/kb/195565/en-us). The more rows that your table contains, the more changes that need to be made for them to automatically refresh – on large tables, you may need to set up more frequent stats updates.

You also want to be aware that using table variables on large result sets can be wildly inaccurate – they always estimate a low number of rows.

TRIVIA: WHICH TYPE OF PLAN IS STORED IN THE PLAN CACHE?

The estimated plan is stored in the plan cache. If you review the XML (doesn’t that sound like fun?!), you will see “ParameterCompiledValue” listed near the end. This is what value the query was run with when the plan was stored. Ensuing executions may use different values, which can lead to less-than-optimal performance if bad parameter sniffing happens.

 

An example of compiled values in the execution plan XML

An example of compiled values in the execution plan XML

Estimated execution plans can be very useful as you are writing and tuning queries, giving you an idea of how SQL Server will most likely perform query execution. However, if you need to know exactly what steps SQL Server will take, executing the query and reviewing the actual execution plan is the only way to be certain.

Learn More in Our Execution Plan Training

Our How to Read Execution Plans Training explains how to get an execution plan, how to read the operators, and learn solutions to common query problems.

Capturing Deadlocks in SQL Server [Video]

If you’re experiencing deadlocks in SQL Server, you want to know more about them. What queries are causing them? What tables and indexes are at the root of the problem? You can find this information by using tools such as trace flags, Profiler, and Extended Events. Jes shows you how in this 25-minute video:

Download the scripts here.

For more videos like this:

css.php