SQL Server 2012 SP3 has just been freshly released! Not only does this release contain bug fixes, it also contains a slew of performance tuning related features.
Many of these features are about memory grants. This is NOT the memory used in the buffer pool/ data cache by the query — it’s the memory also known as Query Workspace Memory. Start reading more here.
Check these out:
KB 3107398 – Improvements to sys.dm_exec_query_stats
This is kind of a big deal. The sys.dm_exec_query_stats DMV will now report on total, last, min and max statistics for granted workspace memory, ideal workspace memory, degree of parallelism, and reserved threads. These columns are in SQL Server 2016 CTP3, but it’s really awesome that they’ve been backported town to 2012!
KB 3107397 – “Actual Rows Read” Added to Query Execution Plans
This is really exciting! It’s hard for people to read execution plans. You may have a very large index seek or scan that reads a ton of rows, and it has a hidden filter in the seek or scan. But it may be hard to diagnose because you only see the number of rows to come OUT of the seek or scan operator. This lets you know not only the rows that exit the operator, but how many it had to consume, too. It’s sorta like having a little bit of STATISTICS IO in your execution plan!
KB 3107401 – New Query Hints for min_grant_percent and max_grant_percent
Previously, if you identified that a query was asking for a problematic memory grant, your options were pretty limited – code changes, index changes, resource governor, or server wide settings. These options let you
shoot yourself in the foot tune this just for a single query. Check out the KB– there is a safety in the feature. If the minimum required grant is higher than max_grant_percent, it will still get the minimum.
KB 3107172 – hash_spill_details Added to Extended Events to Diagnose tempdb Spills, Improvements to sort_warning and hash_warning
Ever seen sort or hash spill warnings in execution plans and wondered how much of a difference they were making to the runtime of the plan? That may be less of a mystery with these improvements.
KB 3107173 – query_memory_grant_usage added to Extended Events
This XEvent allows tracing for the ideal memory grant for queries, what they actually got, and the degree of parallelism they used.
What About SQL Server 2014?
I don’t believe any of these are currently available for SQL Server 2014, but looking at past release patterns it’s likely they’ll be available soon.
PSA: Don’t Forget to Test Your Service Packs
Even the best laid plans can sometimes go awry.
We sometimes help clients interview DBAs and Developers for their team. Requirements vary depending by position, but sometimes it’s important to test a candidate’s TSQL skills.
While I can test candidates thoroughly on strategies for TSQL (when they’d use certain constructs, the pros and cons of various approaches), if what you care about is skills writing complex TSQL there’s one surefire test: a lab.
The first step is to pick your dataset and environment, then dream up the TSQL query you’d like your candidate to write. Next, test it with one of your own staff members and make sure the problem is suitable for the job you’re trying to fill.
But you may not want to give candidates access to your production, or even development data — after all, they don’t work for you yet.
Good news, there’s an easy way to do this online! Check out the Stack Exchange Data Explorer. You can assign the candidate a query to write using this tool online.
How to make it work:
- This only works for in-person interviews. Because human nature.
- You’re going to need a moderator and at least 30 minutes, because the candidate will need to figure out the schema.
- A history of queries is kept in the tool. You’ll need to let them know they can’t search that history, and it’s up to you if you’re going to let them use Books Online (hey, we all use it in the real world).
If you don’t want to deal with the hassle of the query history online, you can always download a copy of the Stack Overflow database and set up your problem on a locally installed test instance of SQL Server, too.
The secret test: if your candidate likes writing TSQL, they’re going to love the challenge and it will energize them.
One of the great things about the SQLPASS Summit is the annual Women in Technology lunch. Not only does the lunch feature an interesting presentation, but you get to talk to men and women at your table about diversity, how to build better teams, and how we can help others.
Many of the best moments at conferences happen when you connect with your peers. The two things I found most fascinating at the WIT lunch this year came from my table and from audience questions.
We can give back. Kathi Kellenberger’s doing it!
I was lucky to sit at a table next to Kathi Kellenberger. She shared with me that she volunteers as a mentor for LaunchCode. Kathi volunteers as part of this free, non-profit program to inspire and teach talented people and help them land jobs in IT. She trains her group regularly in person and provides awesome, free online resources for CoderGirls.
I’m really inspired by the work Kathi is doing. Rock on, Kathi!
Harvard offers free tools to test for unconscious bias
Cindy is on the Azure CAT team, and has recently begun presenting to people and encouraging us all to take free online tests to help us identify our unconscious bias as a first step to building better, happier teams.
Thanks PASS, for hosting such a terrific lunch. I’m already looking forward to attending next year.
Earlier this week I had a great time teaching a day on index tuning at the 2015 SQL PASS Summit. Along the way I got great questions! Here’s the links that came up to answer them:
The Data Loading Performance Guide by Thomas Kejser, Peter Carlin and Stuart Ozer.
Deleting LOB Data and Shrinking the Database by Denny Cherry.
Slow in the Application, Fast in SSMS by Erland Sommarskog.
Conor vs. “Does Join Order Matter?” by Conor Cunningham.
Connect Item: Rebuilding an index clears stats from sys.dm_db_index_usage_stats by Joseph Sack.
Limitations in Indexed Views from Books Online.
You’ll learn what you need to script and document from your current instance, which settings you should change after your migration, required tests for new installations, and better alternatives to in-place upgrades.
Whether your SQL Server 2005 instance is hitting end of life or you’re planning an upgrade to the latest and greatest, our migration checklist will save you time and frustration.
Watch this free video online now at the Veeam site here (registration required).
Download the SQL Server Migration Checklist as an eBook from our free First Responder Kit.
How hard is it for a systems administrator who’s used to running SQL Server on Windows Clusters to tackle Availability Groups? Our example system administrator knows a bit of TSQL and their way around Management Studio, but is pretty new to performance tuning.
Well, it might be harder than you think. First, let’s look at the skills needed to succeed. Then let’s tackle a sample training plan to get those skills.
Do You Have the Skills to Manage AGs? Take this Test!
sp_Blitz® Sanity Test
- Have you eliminated all warnings regarding backups, CHECKDB, alerts, and other administration basics that our free sp_Blitz® script warns about? (Not thought about eliminating them, actually eliminated them.)
- Have you established clear RPO and RTO for the databases you’re considering for AGs and made sure your backups and restore plans are meeting them? (Not an estimate, actual sign-offs by business owners.)
Incident Management Skills Test
- Do you regularly use techniques to learn what’s running and how long it’s been waiting at any given point in time that are NOT SQL Server’s Activity Monitor or sp_who/sp_who2? (Sorry for the bias, but Activity Monitor is full of lies, and those SQL Server 2000 tools don’t really help anymore.)
- Do you have a documented incident response process to collect data when performance or availability suffers that at least 2 people can run?
- Do you regularly execute a process to perform root cause analysis when performance is bad or the SQL Server is offline? (Only answer “yes” if it has successfully led to a real root cause 3 times in the last year.)
Performance Tuning Skills Test
- Do you regularly analyze your SQL Server wait statistics and baseline them? (A “yes” means that you know what your top 10 bottlenecks mean, what “poison” waits are and what they mean if they show up, and you could quickly identify any changes in your top waits and react.)
- Do you know the top 10 queries that run against your SQL Server by CPU, and have you tuned their indexes?
SQL Server Scripting/Configuration Skills Test
- Do you have the scripting skills to be able to handle managing jobs across all nodes? Jobs must all run everywhere, and be smart enough to know who is the primary replica.
- Do you have the scripting skills and security knowledge to be able to manage linked servers and logins across all replicas? (Don’t say yes if you haven’t done it before– it’s trickier than you think!)
Windows Clustering Skills Test
- Have you successfully executed basic Windows clustering management tasks with the version of Windows you plan to go live on? These tasks are: configuring quorum, getting clean cluster validation reports, and using the Cluster Failover Manager.
- Have you practiced advanced cluster rescue scenarios at least twice within the past year? (These tasks are: forcing quorum, generating and reading cluster logs)
Testing and Patching Skills Test
- Are you subscribed to and do you actively review all hotfixes that come out for your version of Windows Failover Clustering and SQL Server?
- Do you test and apply hotfixes and selective updates to a test environment on a regular basis as part of a patching deployment for production?
Here’s the deal: to do well with Availability Groups you need to honestly say yes to every single question on this list.
Closing the Gap: A Training Plan
The right training plan for Availability Groups includes learning from others as well as hands-on experience. To make this work, you need to be devoting at minimum 12 full days per quarter to learning. That’s at least one full day a week, and even then, your time may be very, very tight.
First Quarter: Learn the Pitfalls of AGs and Build your Incident Response Process
If you didn’t score two “yes’s” on the sp_Blitz® sanity test, that’s priority #1. Make sure you have completely covered that section before moving on.
Next, make sure you understand why you need to learn so much, and also make sure you really need AGs! Dig in and learn the basics about Availability Groups and why they’re so tricky, and how to choose the right HA/DR solution in SQL Server. Options:
- Free option: Get reading on our blog, we’ve got tons of articles.
- Video training option: Take our $299 online course – The DBA’s Guide to High Availability and Disaster Recovery
Next, get working in production and build your incident response kit. It will take work and time to get incident response and root cause analysis really working, but this is an absolute pre-requisite for working with AGs.
Second Quarter: Baseline and Analyze Wait Stats and top Queries
Focus on establishing lightweight monitoring and baselining your top waits and queries in SQL Server.
- Free tools: sp_AskBrent® helps you with wait information and sp_BlitzCache® helps you with queries
- Books and blog posts: We have a list to get you started here
- Video training option: Our Developers Guide to SQL Server Performance class walks you through it for $299.
Third Quarter: Build a Prototype and Get Advanced Training
At this point, you’ve learned a lot about how to understand what SQL Server is telling you. It’s time to start understanding Availability Groups specifically.
- Review your notes from what you learned about looking out for in AGs– and make sure you plan the right version of Windows and SQL Server that’s really right to use.
- Set up a prototype environment. You need the same hardware and storage that you’re going to use in production. This isn’t a temporary environment, either: to live well with AGs you need a pre-production environment!
- Restore full sized production databases to your AG environment and generate activity against it.
- Make the environment fail! You need to cause failures in multiple places and validate that your incident and performance management tools really work in this brave new world.
- Oh, and work on all those scripting skills, too.
At this point in the process, it’s time to learn from others and really make sure you’re on the right track. You can do this in multiple ways:
- Attend a SQL Server conference that lets you attend “lessons learned” style panels– such as the SQL PASS Summit
- Attend an advanced SQL Server DBA training class like our Senior DBA course
Fourth Quarter: Reassess Your Progress and Fill the Gaps
You’ve come a long way. But you still had a job to do with other tasks in it.
Step back and re-take the quiz. How are you executing on all of those tasks, and how many people can execute on them? Return to the areas where you’ve scored the weakest and build those skills up.
Yep, it Takes a Year
Availability Groups are a very cool, cutting edge tool in SQL Server. They’re expensive, but people are willing to pay the price because availability and performance really matter for them.
But if you’re not careful, the tool that you’re using to try to improve performance can slow you down– or take you offline entirely. And if you don’t have really strong incident management and performance tuning skills, you’ll have no idea if the problem is the AG or something completely unrelated.
The good news is that if you have a foundations in system administration, you know some TSQL, and you really focus your efforts, you can learn these skills in a year!
Not Sure Where to Find the Time?
Show your manager this post and talk about your answers to the questions above, and the fact that you need to fill the gap. Be clear about areas where you and your team are weak and strong, and what risks you’re concerned about if you take on more complex technology that you can’t handle. It may not always be obvious, but usually your manager wants you to succeed as much as you do!
Got questions about how to design indexes for performance in SQL Server? You’re not alone! Join Kendra to get answers to the top questions she gets about indexes. You’ll learn how to decide on key column order, how to tell if you should drop an index, whether it’s bad to use the clustered index key in your nonclustered index definition, and more.
An interesting question came up in our SQL Server Performance Tuning course in Chicago: when creating an indexed view, does it require an exclusive lock on the underlying table or tables?
Let’s test it out with a simple indexed view run against a non-production environment. (AKA, a VM on my laptop running SQL Server 2014.)
Tracing the Locks
To trace the locks, I just used one of the built-in XEvents templates tor tracing locks, added a filter so it only looked at locks from my current session (53), and set it to write to an event file. The query scripts out like this:
CREATE EVENT SESSION [LockTrace] ON SERVER ADD EVENT sqlserver.lock_acquired ( SET collect_database_name=(1),collect_resource_description=(1) ACTION(sqlserver.query_hash) WHERE ( [package0].[greater_than_uint64]([database_id],(4)) AND [package0].[equal_boolean]([sqlserver].[is_system],(0)) AND [package0].[equal_uint64]([sqlserver].[session_id],(53)) ) ) ADD TARGET package0.event_file (SET filename = N'S:\Xevents\Traces\LockTrace.xel') 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
Creating the Indexed View
I test it out by creating and dropping the indexed view this way:
CREATE VIEW dbo.IndexMe WITH SCHEMABINDING AS SELECT PostTypeId, COUNT_BIG(*) as CT FROM dbo.Posts GROUP BY PostTypeId; GO -- This is where it becomes an indexed view. CREATE UNIQUE CLUSTERED INDEX ix_indexme on dbo.IndexMe (PostTypeId) GO DROP INDEX ix_indexme on dbo.IndexMe; GO
Now let’s analyze the locks!
I stop my trace and open up the event file. I click around in the magic XEvents toolbar and group by object_id and mode (that’s the lock mode). And here’s what I get:
The low object_ids are internal system tables. Object_id 526624919 =dbo.Posts. Object_id 843150049 = dbo.IndexMe.
Translating this out:
- dbo.Posts required S and SCH_S locks
- dbo.IndexMe required X, SCH_S, and SCH_M locks
We didn’t require exclusive locks on the underlying table. I did require shared locks, and creating this indexed view could require lots of IO or be part of a blocking chain with other transactions. But creating the indexed view only needed exclusive locks on the indexed view itself.
Change is exciting, and a little scary, too. If you’re looking to upgrade or migrate your SQL Server, head on over to our events page and get registered for our upcoming free webcast on Thursday, Oct 15 at 10:00AM Central, 11:00AM Eastern.
SQL Server Migration Checklist (Sponsored by Veeam)
It’s time to upgrade your SQL Server! But do you know exactly what you need to do? Join this free webinar with Microsoft Certified Master Kendra Little to get her checklist to make your migration go smoothly. You’ll learn what you need to script and document from your current instance, which settings you should change after your migration, required tests for new installations, and better alternatives to in-place upgrades. Whether your SQL Server 2005 instance is hitting end of life or you’re planning an upgrade to the latest and greatest, this migration checklist will save you time and frustration. Register now.
Missed the Webcast, but want the Migration Checklist?
Download the SQL Server Migration Checklist eBook as part of our free First Responder Kit.
Filtered indexes can be tricky. Just getting your queries to use the filtered index can be a real pain. Parameterization may mean it’s “unsafe” to use the filtered index. The optimizer might want the filtered column in your key or include column list when it doesn’t seem like it should have to be there. The weather might be cloudy.
But there’s one type of filtered index that everyone can love for a couple different reasons: IS NOT NULL.
“IS NOT NULL” filter for a selective query…
Here’s an example index. We’re using the StackOverflow sample database and creating the index only on Posts which are closed (a small subset):
CREATE INDEX ix_Posts_Score_ClosedDate_INCLUDES_FILTERED on dbo.Posts (Score, FavoriteCount, ClosedDate) INCLUDE (Id, Title, Tags, CommentCount, OwnerUserId, LastEditDate, LastEditorDisplayName) WHERE (ClosedDate IS NOT NULL); GO
Here’s a query looking for Posts with a ClosedDate in a given range. Note that the query does NOT say “ClosedDate is NOT NULL”, it’s just specifying a value range:
CREATE PROCEDURE #ClosedPostsByScoreAndFavorites @Start DATETIME, @End DATETIME, @Score INT, @FavoriteCount INT AS SELECT p.Id, u.DisplayName, p.Title, p.Tags, p.CommentCount, p.FavoriteCount, p.Score, p.ClosedDate, p.LastEditDate, p.LastEditorDisplayName FROM dbo.Posts as p JOIN dbo.Users as u on p.OwnerUserId=u.Id WHERE p.ClosedDate between @Start and @End and p.Score > @Score and p.FavoriteCount > @FavoriteCount GO
We give the query a run and look at the plan….
EXEC #ClosedPostsByScoreAndFavorites @Start='2011-01-01', @End='2014-01-01', @Score=2, @FavoriteCount=2; GO
Woooo, it matches my index!
And now for my next trick… what if you need to guarantee uniqueness for a column that allows NULLS?
We’re entering controversial territory here, so I’m going to be careful. SQL Server treats NULL as a value. This is a really big deal to some people, and I totally understand if you want to vent in the comments about how that’s a terrible thing and it shouldn’t be that way, and SQL Server stole your pickup truck. But it’s the SQL Server we live in.
Treating NULLs as a value means that I can’t create a unique index or a unique constraint on a column that allows NULLs if it has more than one row with NULL in it. More than one NULL means I have duplicates.
A filtered unique index can get you around the problem, like this:
CREATE UNIQUE INDEX uq_UsersAllowNulls_DisplayName on dbo.UsersAllowNulls ( DisplayName ) WHERE DisplayName IS NOT NULL; GO
Yay for indexes!
We’ve got tons of material on indexes, start reading more over here.