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.
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.
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.
Expanding the properties for the compute scalar, I see the function, but it says there is only one execution.
A quick glance at my Extended Events live feed tells a different story.
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.
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.
The Extended Events session again shows 19,972 executions – once for each row in the index.
The data isn’t filtered out until after the function is called, so it is executed once for each row.
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.
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.
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.
Let’s look at the command more closely.
EXEC msdb.dbo.sp_send_dbmail @profile_name = 'Main DB Mail profile', @recipients = 'email@example.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:
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!
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!
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.
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?
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.
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.
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.
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.
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.
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.
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:
For more videos like this:
What’s a deadlock? Well, let’s say there’s a fight going on between Wonder Woman and Cheetah, and, in the same room, a fight between Batman and Mr. Freeze. Wonder Woman decides to help Batman by also attempting to throw her lasso around Mr. Freeze; Batman tries to help Wonder Woman by unleashing a rope from the grappling gun at Cheetah. The problem is that Wonder Woman already has a lock on her opponent, and Batman has his. This would be a superhero (and super) deadlock.
When a deadlock occurs in SQL Server, two or more tasks are running and holding locks on data. Then, each task requests to lock the data the other task is already holding. Both tasks wait for the other to give up. Neither does. SQL Server could let this showdown continue indefinitely, but it won’t. It picks one task – usually, whichever will be the least expensive to roll back – as the victim, and that task is killed.
How do I know if it’s happening to me?
You might notice slowness in your application. Users might complain about slowness or error messages. And, you’ll see a message in the SQL Server Log that says “Transaction (Process ID 103) was deadlocked on resources with another process and has been chosen as the deadlock victim.”
Your server has encountered a deadlock.
How can I capture more information about it?
You have several options: you can enable a trace flag to write more information to the log, you can capture deadlocks graphs using Profiler or Extended Events, and you can track the number of deadlocks occurring using Performance Monitor.
Use trace flags to write to the log
Two trace flags can be enabled to capture more information in the log: 1204 and 1222. 1204 lists the information by node; 1222 lists it by process and resource. You can enable both simultaneously. To enable the flags, use the command
DBCC TRACEON (1204, 1222)
Here’s a small sample of what would appear in the log:
No one wants to read through that, manually parsing it to figure out what database it occurred in, what tasks were involved, and which was the victim. Wouldn’t it be great if this was represented graphically?
Capture a deadlock graph with Profiler or Extended Events
It is – you just have to know where to look to for a deadlock graph. In SQL Server 2005, 2008, and 2008R2, I prefer to use a Profiler trace; in SQL Server 2012 and 2014 I rely on Extended Events.
When you set up a Profiler session to gather deadlock information, select the “Deadlock graph”, “Lock:Deadlock” and “Lock:Deadlock Chain” events. Make sure you go to the Events Extraction Settings tab and select the option “Save Deadlock XML events separately”. This ensures that the .xdl files – the graphs – will be saved as separate files. When you start the session, if a deadlock occurs, you will see “Deadlock graph” captured as an event.
The task with the blue X on it is the victim. By holding your mouse over the process oval, you can see what statement was being executed by that task. The Key Lock rectangles will help you find the object and index that the locking and blocking occurred on.
In the future, Profiler will be removed from SQL Server. Extended Events (EE) is taking its place. You have two options for getting deadlock information using EE. First, there is the system_health session that, by default, runs continuously. You can mine this for deadlocks that have happened in the past. You can also set up your own session to capture only deadlock information (and any other relevant events).
If using the system_health session, filter on “xml_deadlock_report”. If setting up your own session, capture the same event. If you are using the GUI available in 2012 and 2014, you can open the file and view the deadlock graph on the “Deadlock” tab.
If parsing the results using T-SQL and XQuery, you would use the .query method to extract the deadlock XML, like such:
SELECT DeadlockEventXML.query('(event/data[@name="xml_report"]/value/deadlock)') AS deadlock_graph
This generates the XML, which you would then save as an .xdl, then close and re-open with SSMS to view the graph.
Track Performance Counters
Performance Monitor (PerfMon) is valuable for tracking many statistics. One of the metrics you can track for SQL Server is SQLServer:Locks – Number of Deadlocks/sec. This is the least informational of the methods mentioned here – it only gives you a count of how many are happening per second in your system. It can be helpful, however, to track this to see what times of the day produce the most deadlocks. You can then use the other methods mentioned here to research what is happening during that time to cause the deadlocks.
Don’t be a victim!
Take charge of your SQL Servers! Become familiar with detecting deadlocks, finding out what caused them, and fixing them! For more tools and advice, visit Locking and Blocking in SQL Server.
What secrets lie within SQL Server’s error log? Many! This day-by-day record of your server’s actions can be a treasure trove of information. Here you’ll learn what to look for, how to effectively search it, and how to get alerted of potential problems.